sql - 执行查询中创建的多个 SQL 字符串

Question

我正在尝试为数据库中的（几乎）所有表动态创建审计表。我可以动态生成适当的 SQL，如下所示：

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;

这给了我一系列表格的行：

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);

等等我正在努力解决的实际上是执行那些动态生成的查询。搜索EXECUTE似乎是必需的功能，但如果不产生语法错误或什么都不做，我就无法让它工作。我会欣赏正确方向的观点。

标签： sqlpostgresqlplpgsqldynamic-sql

Answer 1

您可以在语句EXECUTE的循环中使用动态 SQL ：DO

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
$$;

我先扔了一个RAISE NOTICE来检查有效载荷。
取消注释EXECUTE要实际执行的行。

您quote_ident(table_name) 在附加“_audit”之前有过。对于所有实际需要双引号的表名，这将失败。你必须这样做quote_ident(table_name || 'audit')。但我format()改为使用。更方便。看：

• 将表名和列名定义为 plpgsql 函数中的参数？

我也使用pg_catalog.pg_tables而不是information_schema.tables. 更快，正是您所需要的。看：

• 如何检查给定模式中是否存在表

我取消了子查询 - 不需要。