E
E
eliasum2022-01-20 11:54:38
PostgreSQL
eliasum, 2022-01-20 11:54:38

Where is the error in EXECUTE FORMAT?

There is a table with data and a function:

CREATE TABLE IF NOT EXISTS in_table(
in_key INTEGER NOT NULL,
in_tst TIMESTAMPTZ NOT NULL,
in_val FLOAT NOT NULL);

INSERT INTO in_table (in_key, in_tst, in_val) 
  SELECT
    (random()*30)::INT, tstamp, random()*80 - 40
  FROM
    generate_series(
      NOW() - INTERVAL '90 days',
      NOW(),
      '1 min'
    ) AS tstamp;

CREATE OR REPLACE FUNCTION _Foo(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE  
  
BEGIN

  RETURN QUERY SELECT in_key, in_tst, in_val 
  FROM in_table 
  WHERE in_tst BETWEEN st AND fin;

END;

$$ LANGUAGE plpgsql;


How to rewrite the function so that it dynamically generates a table return, something like this "pseudo-code":
CREATE OR REPLACE FUNCTION _Foo(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE  
  
BEGIN

  EXECUTE FORMAT('
  RETURN QUERY SELECT %I, %I, %I
  FROM %I 
  WHERE %I BETWEEN %I AND %I;
  ', in_key, in_tst, in_val, in_table, in_tst, st, fin);

END;

$$ LANGUAGE plpgsql;

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Akina, 2022-01-20
@eliasum

CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE  
  
BEGIN

  RETURN QUERY EXECUTE FORMAT('
  SELECT %I, %I, %I
  FROM %I 
  WHERE %I BETWEEN ''%s'' AND ''%s'';
  ', 
  'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);

END;

$$ LANGUAGE plpgsql;

DEMO fiddle
Find errors yourself...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question