Execute multiple SQL strings created in query

Question

I am trying to dynamically create audit tables for (almost) all tables in the database. I can generate the appropriate SQL dynamically, like so:

SELECT                          
    'CREATE TABLE IF NOT EXISTS '
    || tab_name || '_audit(timestamp TIMESTAMPTZ NOT NULL, entity JSONB NOT NULL);'
FROM (
    SELECT                                                                     
        quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name
    FROM                         
        information_schema.tables
    WHERE                                                       
        table_schema NOT IN ('pg_catalog', 'information_schema')
        AND table_schema NOT LIKE 'pg_toast%'
) tablist;

This gives me a series of rows of the form:

CREATE TABLE IF NOT EXISTS public.table1_audit(timestamp TIMESTAMPTZ NOT NULL, entity JSONB NOT NULL);
CREATE TABLE IF NOT EXISTS public.table2_audit(timestamp TIMESTAMPTZ NOT NULL, entity JSONB NOT NULL);

Etc. What I am struggling with is actually executing those dynamically generated queries. From searching EXECUTE seemed to be the required function, but I could not get it to work without either producing a syntax error, or just doing nothing. I would appreciate a point in the right direction.

Does this answer your question: dynamic sql query in postgres? — Parfait
– Parfait, Commented Jun 29, 2021 at 23:48
If you're using psql, leave off the ; at the end of the sql statement and run \gexec — Jeremy
– Jeremy, Commented Jun 30, 2021 at 0:11
That's the thing, I'm actually trying to write this into a django manual migration, so not via psql — Alex
– Alex, Commented Jun 30, 2021 at 0:12

Erwin Brandstetter · Accepted Answer · 2021-07-01 02:16:46Z

3

You can use dynamic SQL with EXECUTE in a loop in a DO statement:

DO
$$
DECLARE
   _sql text;
BEGIN
   FOR _sql IN
      SELECT format('CREATE TABLE IF NOT EXISTS %I.%I(timestamp timestamptz NOT NULL, entity jsonb NOT NULL)'
                  , schemaname
                  , tablename || '_audit')
      FROM   pg_catalog.pg_tables  -- only tables and partitioned tables, no toast tables
      WHERE  schemaname NOT IN ('pg_catalog', 'information_schema')
   LOOP
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql;
   END LOOP;
END
$$;

I threw in a RAISE NOTICE to inspect the payload first.
Uncomment the EXECUTE line to actually execute.

You had quote_ident(table_name) before appending '_audit'. That would fail for all table names that actually require double-quoting. You'd have to do quote_ident(table_name || 'audit'). But I use format() instead. More convenient. See:

Define table and column names as arguments in a plpgsql function?

I also use pg_catalog.pg_tables instead of information_schema.tables. Faster, and exactly what you need. See:

How to check if a table exists in a given schema

And I scrapped the subquery - not needed.

edited Jul 1, 2021 at 2:16

answered Jun 30, 2021 at 0:26

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

Thank you, that's exactly what I was after!

Collectives™ on Stack Overflow

Execute multiple SQL strings created in query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related