postgresql - PL/pgSQL 循环遍历多个模式、表和行

Question

我有一个包含多个相同模式的数据库。每个模式中都有许多名为“tran_...”的表。我想遍历所有模式中的所有“tran_”表并提取特定日期范围内的记录。这是我到目前为止的代码:

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 实际上是模式限定的(两个 separate 标识符: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您可以直接返回动态查询的结果。示例:

  • Return SETOF rows from PostgreSQL function
  • Refactor a PL/pgSQL function to return the output of various SELECT queries

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

  • Table name as a PostgreSQL function parameter

• 注意我是如何分别转义表和模式的:

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

• 因此，我只是使用 %s 在后面的查询中插入已经转义的表名。和 %L 将其转义为输出的字符串文字。

• 我喜欢在参数和变量名称前加上 _ 以避免与列名称发生命名冲突。无其他特殊含义。

与您的原始功能相比，略有不同。这一个返回一个转义标识符(仅在必要时用双引号引起来)作为表名，例如:

"WeIRD name"

代替

WeIRD name

更简单

如果可能，使用inheritance完全消除对上述功能的需要。完整示例:

• Select (retrieve) all records from multiple schemas using Postgres