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

1

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.

edited Jan 11, 2021 at 22:46

answered Jan 11, 2021 at 22:40

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

0

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;

answered Jan 12, 2021 at 2:25

ziggy

1,5586 gold badges26 silver badges57 bronze badges

Collectives™ on Stack Overflow

Postgres execute select query created by string_agg

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related