sql - plpgsql dynamically add select query to variable

Question

I am attempting to loop through a varchar array with many schemas and dynamically construct a massive select statement. the 1st 2 raise notices work fine but I do not know how append the dynamic select statement into one large select.

Ideally I want something like this

SELECT 
    st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom
    ,parcel_id
FROM schema1.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269))
UNION
SELECT 
    st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom
    ,parcel_id
FROM schema2.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269))

the code

DO
$do$
DECLARE
   a varchar[] := array['schema1','schema2'];
   i integer; 
   slct varchar;
BEGIN
   FOR i IN 1 .. array_upper(a, 1)
   LOOP
      RAISE NOTICE '%', a[i]; 
      RAISE NOTICE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
                      FROM %.parcel t join bounds b
                        on st_intersects(t.geom,st_transform(b.geom,4269))',a[i];
      EXECUTE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
                      FROM %.parcel t join bounds b
                        on st_intersects(t.geom,st_transform(b.geom,4269))',a[i] into slct;         
   END LOOP;
END
$do$;

the error

ERROR:  query "SELECT 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
                      FROM %.parcel t join bounds b
                        on st_intersects(t.geom,st_transform(b.geom,4269))',a[i]" returned 2 columns
CONTEXT:  PL/pgSQL function inline_code_block line 17 at EXECUTE
SQL state: 42601

updated qry and error

EXECUTE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
              FROM $1.parcel t join bounds b
                on st_intersects(t.geom,st_transform(b.geom,4269))' into slct 
                using a[i];

error

ERROR:  syntax error at or near "$1"
LINE 2:        FROM $1.parcel t join bounds b
                    ^
QUERY:  SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
                      FROM $1.parcel t join bounds b
                        on st_intersects(t.geom,st_transform(b.geom,4269))
CONTEXT:  PL/pgSQL function inline_code_block line 17 at EXECUTE
SQL state: 42601

question from:https://stackoverflow.com/questions/65617257/plpgsql-dynamically-add-select-query-to-variable

Answer 1

If you have the finite amount of schemas then you could to use the plain SQL like

SELECT ...
FROM schema1.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema1' = any(array['schema1','schema2'])
UNION
SELECT ...
FROM schema2.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema2' = any(array['schema1','schema2'])
UNION
SELECT ...
FROM schema3.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema3' = any(array['schema1','schema2'])

If you want to build the query dinamically then you could to do that in single query:

select
    string_agg(format($$SELECT * FROM %I.parcel t 
JOIN bounds b
    ON st_intersects(t.geom,st_transform(b.geom,4269)
$$, shm), e'UNION
') as sql
FROM unnest(array['schema1','schema2']) as shm;

┌───────────────────────────────────────────────────────┐
│                          sql                          │
├───────────────────────────────────────────────────────┤
│ SELECT * FROM schema1.parcel t                       ?│
│ JOIN bounds b                                        ?│
│     ON st_intersects(t.geom,st_transform(b.geom,4269)?│
│ UNION                                                ?│
│ SELECT * FROM schema2.parcel t                       ?│
│ JOIN bounds b                                        ?│
│     ON st_intersects(t.geom,st_transform(b.geom,4269)?│
│                                                       │
└───────────────────────────────────────────────────────┘

Use the result of this query as an argument of the EXECUTE statement.

Several notes.

You could to use dollar-quoted strings to keep syntax highlighting. Compare

select 'select
*
from foo';

select $$select
*
from foo$$;

Use format() function with %I placeholder for identifiers:

select format('%s %I', 'FooBar', 'FooBar');

┌─────────────────┐
│     format      │
├─────────────────┤
│ FooBar "FooBar" │
└─────────────────┘