PL/pgSQL在多个模式、表和行中循环

Question

我有一个包含多个相同模式的数据库。有许多表都命名为"tran_…"在每个模式中。我想循环遍历所有模式中的所有"trans_"表，并提取特定日期范围内的记录。这是我迄今为止的代码：

CREATE OR REPLACE FUNCTION public."configChanges"(starttime timestamp, endtime timestamp)
  RETURNS SETOF character varying AS
$BODY$DECLARE
    tbl_row RECORD;
    tbl_name VARCHAR(50);
    tran_row RECORD;
    out_record VARCHAR(200);
BEGIN
    FOR tbl_row IN
       SELECT * FROM pg_tables WHERE schemaname LIKE 'ivr%' AND tablename LIKE 'tran_%'
    LOOP
        tbl_name := tbl_row.schemaname || '.' || tbl_row.tablename;
         FOR tran_row IN
            SELECT * FROM tbl_name
            WHERE ch_edit_date >= starttime AND ch_edit_date <= endtime
         LOOP
            out_record := tbl_name || '    ' || tran_row.ch_field_name;
                        RETURN NEXT out_record;
         END LOOP;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

当我尝试运行这个时，我得到：

ERROR:  relation "tbl_name" does not exist
LINE 1: SELECT * FROM tbl_name WHERE ch_edit_date >= starttime AND c...

Answer 1

@Pavel已经为您的基本错误提供了修复程序。

但是，由于您的tbl_name实际上是模式限定的（在：schema.table中有两个独立的标识符），因此它不能与format()中的%I一起作为一个整体转义。您必须分别对每个标识符进行转义。

除此之外，我建议采取不同的方法。外循环是必要的，但内循环可以用更简单、更高效的基于集合的方法来代替：

CREATE OR REPLACE FUNCTION public.config_changes(_start timestamp, _end timestamp)
  RETURNS SETOF text AS
$func$
DECLARE
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT quote_ident(schemaname) || '.' || quote_ident(tablename)
      FROM   pg_tables
      WHERE  schemaname LIKE 'ivr%'
      AND    tablename LIKE 'tran_%'
   LOOP
      RETURN QUERY EXECUTE format (
         $$
         SELECT %1$L || '    ' || ch_field_name
         FROM   %1$s
         WHERE  ch_edit_date BETWEEN $1 AND $2
         $$, _tbl
         )
      USING _start, _end;
   END LOOP;
   RETURN;
END
$func$  LANGUAGE plpgsql;

• 您必须使用动态SQL来参数化标识符（或代码），就像@Pavel已经告诉您的那样。使用RETURN QUERY EXECUTE，您可以直接返回动态查询的结果。示例：

  • 从PostgreSQL函数返回SETOF行
  • 重构PL/pgSQL函数以返回各种SELECT查询的输出

• 请记住，在动态SQL中，标识符必须被视为不安全的用户输入，并且必须始终进行清理，以避免语法错误和SQL注入:

  • 表名作为PostgreSQL函数参数

• 注意我如何分别转义表和模式：

   quote_ident(schemaname) || '.' || quote_ident(tablename)
  

• 因此，我只使用%s在后面的查询中插入已经转义的表名。和%L来转义它一个字符串文本以供输出。

• 我喜欢在参数和变量名前面加上_，以避免与列名的命名冲突。没有其他特殊意义。

与您原来的功能相比有一点不同。这个返回一个转义标识符（仅在必要时使用双引号）作为表名，例如：

"WeIRD name"

而不是

WeIRD name

要简单得多

如果可能，请使用继承来完全消除对上述函数的需要。完整示例：

• 使用Postgres从多个模式中选择（检索）所有记录

Answer 2

不能将plpgsql变量用作SQL表名或SQL列名。在这种情况下，您必须使用动态SQL:

FOR tran_row IN
   EXECUTE format('SELECT * FROM %I
                   WHERE ch_edit_date >= starttime AND ch_edit_date <= endtime', tbl_name)
LOOP
   out_record := tbl_name || '    ' || tran_row.ch_field_name;
   RETURN NEXT out_record;
END LOOP;