sql_queries/do_create_views.sql file with the following desired content: CREATE FUNCTIONĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL t -output=./sql_queries/do_create_views.sql: 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=$" \ 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 ĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL ĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefix1_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL ĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_0" AS SELECT id, 1 AS factor FROM "prefix_table" WHERE factor IS NOT NULL ĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_1" AS SELECT id, 2 AS factor FROM "prefix_table" WHERE factor IS NOT NULL ĬREATE MATERIALIZED VIEW IF NOT EXISTS "prefixN_table_20" AS SELECT id, 1048576 AS factor FROM "prefix_table" WHERE factor IS NOT NULL How could I generate all my queries using a for loop? HINT: If you want to discard the results of a SELECT, use PERFORM instead.ĬONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statementĪnd if I replace SELECT by PERFORM, I end up with a single word "DO" dumped in my file. Notice also that this SELECT statement already loops over N tables.įrom what I've read so far, this would be the kind of structure in which to embed my code: DO $$īut when I do so and actually execute it, I got this error message: ERROR: query has no destination for result data 'FROM "', table_name, '" WHERE factor IS NOT NULL 'ĪND table_schema not in ('information_schema', 'pg_catalog') '" AS SELECT id, POWER(2,', $i,') AS factor ', 'CREATE MATERIALIZED VIEW IF NOT EXISTS "', Which file is then executed to actually run these queries. Briefly, the idea is to generate some SQL code as a temp table that I can dump to a file. 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.
0 Comments
Leave a Reply. |