Postgresql – Generate view queries using a loop

postgresql

I have this PostgreSQL query where I have 2x $i in a bash-like notation to make it comprehensible that I want to print out a number i ranging from 0 to 20 at these positions using a for loop. Briefly, the idea is to generate some SQL code as a temp table that I can dump to a file. Which file is then executed to actually run these queries.

SELECT CONCAT(
    'CREATE MATERIALIZED VIEW IF NOT EXISTS "',
    table_name, '_factor', $i,
    '" AS SELECT id, POWER(2,', $i,') AS factor ',
    'FROM "', table_name, '" WHERE factor IS NOT NULL;'
)
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Notice also that this SELECT statement already loops over N tables.

From what I've read so far, this would be the kind of structure in which to embed my code:

DO $$
BEGIN
    FOR i IN 0..20 LOOP
        -- insert the previous query here
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
END; $$

But when I do so and actually execute it, I got this error message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

And if I replace SELECT by PERFORM, I end up with a single word "DO" dumped in my file.

How could I generate all my queries using a for loop?

Resulting queries should look like:

CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL;

Precisions: I store this SQL statement in a /sql_queries/generate_views_queries.sql file and execute it to dump the resulting queries in an other file /sql_queries/do_create_views.sql:

psql -d "host=${HOST} port=${PORT} dbname=${POSTGRES_DB} user=${POSTGRES_USER} password=${POSTGRES_PASSWORD}" \
  -f "/sql_queries/generate_views_queries.sql" \
  -t --output="/sql_queries/do_create_views.sql"

PG version is 13.1 from the official PostgreSQL Docker image.

Best Answer

If I understand you correctly below anonymous block should do it.

DO $$
declare 

tabs cursor for
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
  AND table_name NOT LIKE '%suffix$'
  AND table_schema not in ('information_schema', 'pg_catalog')
  AND table_type = 'BASE TABLE'
ORDER BY table_name;
rec record;

query_statement text;
BEGIN

open tabs;
loop
fetch tabs into rec;

exit when not found;

    FOR i IN 0..20 LOOP
         
    query_statement:= 'CREATE MATERIALIZED VIEW IF NOT EXISTS "'||rec.table_name||'_factor'||i||'" AS SELECT id, POWER(2,'||i||') AS factor FROM "'||rec.table_name||'" WHERE factor IS NOT NULL;';
    RAISE NOTICE '%' , query_statement;
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
end loop;
END; $$

What you might need

You should be able to avoid all the complication with a simple SELECT instead:

SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2
FROM   tbl t  -- replace tbl with actual table
JOIN   ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom)
WHERE  t.seq = $age_sequence;

Minor difference: you get the original geom and the intersection geom2 in the result. Add the redundant ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt to the SELECT list if you really need it.

What you ask

I understand your question like this:

I have various input tables, each has a column geom geometry. I want a function to take a table name and clipper_geom geometry as input and return all rows intersecting with my clipper_geom. One column clipped_geom_wkt text shall be appended and the value of geom changed, each showing the intersection with clipper_geom.

That's not trivial. SQL is a static language, a function demands to know the return type at call time at the latest. We can achieve dynamic results with polymorphic types, but we need a well-known row type to work with. The row type of an existing table is good for it, but you want to append another column. That's not easily possible. The workaround is to return the polymorphic row type, plus an extra column (like you seem to have tried already). Decompose the row type in the function call to arrive at your desired result:

CREATE OR REPLACE FUNCTION my_func(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS TABLE (tbl ANYELEMENT, clipped_geom_wkt text) AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);  -- compute once
BEGIN
   FOR tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence  -- data type must match!
       , _geom
   LOOP          
      tbl := tbl #= hstore('geom', ST_Intersection(_geom, tbl.geom));
      clipped_geom_wkt := ST_AsText(tbl.geom);
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call (important!):

SELECT (tbl).*, clipped_geom_wkt
FROM my_func(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Works for me in Postgres 10.

Notable features:

Polymorphic types.
Note how the row variable tbl is defined implicitly in the RETURNS clause, where it copies the input type dynamically (ANYELEMENT). So you are not bound to use SETOF with polymorphic types like you speculated.
Related (you linked to that one yourself):
- Refactor a PL/pgSQL function to return the output of various SELECT queries
FOR-IN-EXECUTE to loop over a dynamic query.
Assignment of a yet-unknown column with the hstore operator #=. See:
- How to set value of composite variable field using dynamic SQL
Decomposition of composite type in the function call with (tbl).*

Simpler

I stuck to your design above, but it's unnecessary complication for the given example. The extra column clipped_geom_wkt is completely redundant, just derive it from geom in the function call. Then we can use the input type directly:

CREATE OR REPLACE FUNCTION my_func2(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS SETOF ANYELEMENT AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);
BEGIN
   FOR _tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence -- data type must match!
       , _geom
   LOOP
      _tbl := _tbl #= hstore('geom', ST_Intersection(_geom, _tbl.geom));
      RETURN NEXT _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT *, ST_AsText(geom) AS clipped_geom_wkt -- if you need the redundant column
FROM   my_func2(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Best Answer

Related Solutions

PostgreSQL – Create Materialized View from Table with JSONB Columns

PostgreSQL Dynamic SQL – How to Define a RETURN Table for Loop

What you might need

What you ask

Simpler

Related Question