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](https://stackoverflow.com/q/12780275/1422451)? — Parfait, Jun 29 '21 at 23:48
If you're using psql, leave off the ; at the end of the sql statement and run \gexec — Jeremy, Jun 30 '21 at 00:11
That's the thing, I'm actually trying to write this into a django manual migration, so not via psql — Alex, Jun 30 '21 at 00:12

Erwin Brandstetter · Accepted Answer · 2021-07-01T02:16:46.253

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.

Thank you, that's exactly what I was after! – Alex Jun 30 '21 at 00:27 — Alex, Jun 30 '21 at 00:27

Execute multiple SQL strings created in query

1 Answers1