Generate view queries using a loop 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 &quot;',
    table_name, '_factor', $i,
    '&quot; AS SELECT id, POWER(2,', $i,') AS factor ',
    'FROM &quot;', table_name, '&quot; 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 &quot;DO&quot; 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 &quot;prefix1_table_0&quot; AS SELECT id, 1 AS factor FROM &quot;prefix_table&quot; WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS &quot;prefix1_table_1&quot; AS SELECT id, 2 AS factor FROM &quot;prefix_table&quot; WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS &quot;prefix1_table_20&quot; AS SELECT id, 1048576 AS factor FROM &quot;prefix_table&quot; WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS &quot;prefixN_table_0&quot; AS SELECT id, 1 AS factor FROM &quot;prefix_table&quot; WHERE factor IS NOT NULL;
CREATE MATERIALIZED VIEW IF NOT EXISTS &quot;prefixN_table_1&quot; AS SELECT id, 2 AS factor FROM &quot;prefix_table&quot; WHERE factor IS NOT NULL;
...
CREATE MATERIALIZED VIEW IF NOT EXISTS &quot;prefixN_table_20&quot; AS SELECT id, 1048576 AS factor FROM &quot;prefix_table&quot; 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 &quot;host=${HOST} port=${PORT} dbname=${POSTGRES_DB} user=${POSTGRES_USER} password=${POSTGRES_PASSWORD}&quot; \
  -f &quot;/sql_queries/generate_views_queries.sql&quot; \
  -t --output=&quot;/sql_queries/do_create_views.sql&quot;

PG version is 13.1 from the official PostgreSQL Docker image.
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 &quot;'||rec.table_name||'_factor'||i||'&quot; AS SELECT id, POWER(2,'||i||') AS factor FROM &quot;'||rec.table_name||'&quot; WHERE factor IS NOT NULL;';
    RAISE NOTICE '%' , query_statement;
    RAISE NOTICE 'factor number is: %', i;
    END LOOP;
end loop;
END; $$