Postgres execute select query created by string_agg

Question

Below is a dummy test query that correctly creates a text version of a SQL query I seek to execute

select string_agg(format($q$SELECT c1,c2 FROM %I.parcel t 
                JOIN bounds b
                    ON 1=1
                $q$, shm), e'UNION\n') as sql
                FROM unnest(array['a','b','c']) as shm

this outputs

SELECT C1,C2 FROM a.parcel t 
                JOIN bounds b
                    ON 1=1
                UNION
SELECT C1,C2 FROM b.parcel t 
                JOIN bounds b
                    ON 1=1
                UNION
SELECT C1,C2 FROM c.parcel t 
                JOIN bounds b
                    ON 1=1

which is correct. What I want to do is actually run the above query. I tried un-nesting it but that did not work. Again this is test data so the contents of the query do not matter

I know I can do this

DO
$do$
BEGIN
execute string_agg(format($q$SELECT c1,c2 FROM %I.parcel t 
                    JOIN bounds b
                        ON 1=1
                    $q$, shm), e'UNION\n') as sql
                    FROM unnest(array['a','b','c']) as shm 
END
$do$;

but its not actually running the SQL text

Daniel Vérité · Accepted Answer · 2021-01-11 22:46:10Z

The query in the DO block is run but its results are discarded.

As mentioned in the doc

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

If you expected the results returned in a single pass as if you had submitted the generated SQL directly, it's not possible with PostgreSQL.

You may either:

make your SQL client retrieve the SQL text and resend it immediately as a query (\gexec in psql does that automatically)
or, inside the code block, feed the results to variables with SELECT INTO or to a temporary table or wherever they fit for further processing.
or, open a refcursor with a hard-coded name in the DO block to browse the results of the query with a FETCH, client-side.

ziggy · Accepted Answer · 2021-01-12 02:25:59Z

I ended getting what I wanted from this -- looped through the select statement and EXECUTE that SQL text into a variable that gets returned

CREATE OR REPLACE FUNCTION test()
RETURNS bytea as
$$
DECLARE
  stmt text;
  result bytea;
BEGIN
  FOR stmt IN
    select string_agg(format($q$SELECT c1,c2 FROM %I.parcel t 
                    JOIN bounds b
                        ON 1=1
                    $q$, shm), e'UNION\n') as sql
                    FROM unnest(array['a','b','c']) as shm 
  LOOP
 
    EXECUTE stmt into result;
    return result;
  END LOOP;
END;
$$
LANGUAGE plpgsql VOLATILE;

Collectives™ on Stack Overflow

Postgres execute select query created by string_agg

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related