Execute text string returned from function as a query

Question

I have a function with the following "signature":

CREATE OR REPLACE FUNCTION iterate_test()
RETURNS text
LANGUAGE plpgsql

...

That returns a query in text form:

SELECT DISTINCT(t1.date) AS date, AMD.adjusted_close AS AMD, GME.adjusted_close AS GME FROM clean_daily AS t1 INNER JOIN clean_daily AS AMD ON t1.date=AMD.date AND AMD.ticker='AMD' INNER JOIN clean_daily AS GME ON AMD.date=GME.date AND GME.ticker='GME' ORDER BY t1.date DESC;

How can I execute this string as a query? I have tried EXECUTE like this:

EXECUTE iterate_test();
EXECUTE QUERY iterate_test();

But I can't get it to work. How do I execute a data query in the form of text?

Full function code:

CREATE OR REPLACE FUNCTION iterate_test()
RETURNS text
LANGUAGE plpgsql
AS
$$
    DECLARE
        temprow record;
        str_query text := 'SELECT DISTINCT(t1.date) AS date';
        prev_table text := 't1';
    BEGIN
        FOR temprow IN SELECT * FROM portfolios WHERE user_name='snigelnmjau'
        LOOP
            str_query := str_query || format(', %s.adjusted_close AS %s', temprow.ticker, temprow.ticker);
        END LOOP;

        str_query := str_query || format(' FROM clean_daily AS t1 ');

        FOR temprow IN SELECT * FROM portfolios WHERE user_name='snigelnmjau'
        LOOP
            str_query := str_query || format(E'INNER JOIN clean_daily AS %s ON %s.date=%s.date AND %s.ticker=''%s'' ', temprow.ticker, prev_table, temprow.ticker, temprow.ticker, temprow.ticker);
            prev_table := temprow.ticker;
        END LOOP;
        str_query := str_query || 'ORDER BY t1.date DESC;';

        --EXECUTE str_query;
        RETURN str_query;
    END;
$$;

Unrelated to your problem, but: `distinct` is **not** a function. It always applies to all columns in the select list. Enclosing one of the columns with parentheses won't change anything and is useless. `distinct (a),b` is the same as `distinct a,(b)` or `distinct a,b` — , Aug 13 '21 at 05:56

score 0 · Answer 1 · answered Aug 13 '21 at 00:39

To answer your question: use RETURN QUERY EXECUTE. See:

Your function with a couple of fixes, including one for the core question:

CREATE OR REPLACE FUNCTION pg_temp.iterate_test()
  RETURNS SETOF text  -- !!
  LANGUAGE plpgsql AS
$func$
DECLARE
   temprow record;
   str_query text := 'SELECT DISTINCT t1.date';  -- !!
   prev_table text := 't1';
BEGIN
   FOR temprow IN
      SELECT * FROM portfolios WHERE user_name = 'snigelnmjau'
   LOOP
      str_query := str_query || format(', %1$I.adjusted_close AS %1$I', temprow.ticker);  -- !!
   END LOOP;

   str_query := str_query || ' FROM clean_daily AS t1 ';  -- !!

   FOR temprow IN
      SELECT * FROM portfolios WHERE user_name = 'snigelnmjau'
   LOOP
      str_query := str_query || format('JOIN clean_daily AS %1$I ON %2$I.date=%1$I.date AND %1$I.ticker=%1$L ', temprow.ticker, prev_table); -- !!
      prev_table := temprow.ticker;
   END LOOP;
   str_query := str_query || 'ORDER BY t1.date DESC';

   RETURN QUERY EXECUTE str_query;  -- !!!!
END
$func$;

But the whole statement may possibly be generated in a single SELECT statement without looping (twice). And why DISTINCT?

Moreover, I am not convinced we need dynamic SQL to begin with. A plain query may be possible.

Execute text string returned from function as a query

1 Answers1