0

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.

0

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; $$
3
  • edited my answer. Try again please. – Burak Yurdakul Feb 23 at 21:07
  • Thanks. I still have a simple "DO" in the resulting file (I've edited the question to actually include how I run the SQL). But commands are actually printed to stdout with a NOTICE: decoration in front of them. – s.k Feb 23 at 21:22
  • If you want to execute command add execute query_statement; in the loop. – Burak Yurdakul Feb 23 at 21:30
0

Follow up!

I've investigated in the direction of a fully fledged PL/pgSQL function, and here's what I've ended with, which is working up to a single unwanted header row:

./sql_queries/generate_views_queries.sql:

CREATE OR REPLACE FUNCTION public.generate_view_queries()
    RETURNS SETOF text -- this did the trick !
AS
$$
BEGIN
    FOR i IN 0..20 LOOP
        RETURN QUERY 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;
    END LOOP;
    RETURN;
END;
$$
LANGUAGE 'plpgsql';

SELECT public.generate_view_queries();

Then, when calling it using psql like:

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

it returns the ./sql_queries/do_create_views.sql file with the following desired content:

CREATE FUNCTION
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;

I only have to call sed after to clean this first row which comes from 'I don't know where' (it sounds like the success message of the function creation):

sed -i 's/CREATE FUNCTION//g' ./create_views.sql

Information about the SETOF return type: https://www.postgresql.org/docs/12/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.