PL/pgSQL函数中的动态SELECT INTO

Question

我如何在Postgres中的PL/pgSQL函数中编写动态的SELECT INTO查询？

假设我有一个名为tb_name的变量，它是从information_schema.tables中的FOR循环中填充的。现在我有一个名为tc的变量，它将获取每个表的行数。我想要类似下面的东西：

FOR tb_name in select table_name from information_schema.tables where table_schema='some_schema' and table_name like '%1%'
LOOP
EXECUTE FORMAT('select count(*) into' || tc 'from' || tb_name);
END LOOP

在这种情况下，tb_name和tc的数据类型应该是什么？

- Karthik

---

请参考手册中的示例：https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN - user330315

Answer 1

CREATE OR REPLACE FUNCTION myfunc(_tbl_pattern text, _schema text = 'public')
  RETURNS void  -- or whatever you want to return
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tb_name information_schema.tables.table_name%TYPE;  -- currently varchar
   _tc      bigint;  -- count() returns bigint
BEGIN
   FOR _tb_name IN
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_schema = _schema
      AND    table_name   ~ _tbl_pattern  -- see below!
   LOOP
      EXECUTE format('SELECT count(*) FROM %I.%I', _schema, _tb_name)
      INTO _tc;      

      -- do something with _tc
   END LOOP;
END
$func$;

注意事项

• 我在所有参数和变量之前添加了下划线（_）以避免与表列发生命名冲突。这只是一个有用的约定。

• _tc 应该是 bigint，因为聚合函数 count() 返回的就是这个类型。

• _tb_name 的数据类型是从其父列动态派生的：information_schema.tables.table_name%TYPE。详见手册中的Copying Types章节。

• 您确定只想要在 information_schema.tables 中列出的表吗？这是有道理的，但请注意潜在的影响。参见：

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

• a_horse 已经 指向手册 并且 Andy 提供了代码示例。这是如何使用 EXECUTE 将动态查询返回的单行或值赋给（行）变量的方式。一个单独的列（例如示例中的 count）会自动从行类型中分解，所以我们可以直接将其赋给标量变量 tc——就像我们将整行赋给记录或行变量一样。相关链接：

  • 如何获取动态生成的字段名的值（PL/pgSQL）

• 在动态查询中限定模式的表名。当前的 search_path 中可能有与相同名称的其他表，如果没有模式限定，那么会导致完全错误（非常令人困惑！）的结果。这是个隐蔽的 bug！或者此模式根本不在 search_path 中，这将立即导致函数引发异常。

  • search_path 如何影响标识符解析和"当前模式"

• 始终正确引用标识符，以防止 SQL 注入和随机错误。模式和表必须分开引用！参见：

  • 将表名作为 PostgreSQL 函数参数
  • 截断 Postgres 数据库中的所有表

• 我在 table_name ~ _tbl_pattern 中使用了正则表达式运算符 ~，而不是 table_name LIKE ('%' || _tbl_pattern || '%')，这样更简单。但无论哪种方式，都要小心模式参数中的特殊字符！参见：

  • PostgreSQL 反向 LIKE
  • 用于正则表达式或 LIKE 模式的转义函数
  • 使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配

• 在函数调用中我为模式名称设置了默认值：_schema text = 'public'。这只是为了方便，您可以选择是否需要。参见：

  • 为类型分配默认值

---

回应你的评论：要传递值，请使用USING子句，例如：

EXECUTE format('SELECT count(*) FROM %I.%I
                WHERE some_column = $1', _schema, _tb_name,column_name)
USING user_def_variable;

相关内容：

• 在触发器函数中插入带有动态表名的语句

- Erwin Brandstetter

---

EXECUTE format('SELECT count() FROM %I.%I', _schema, _tb_name) 我该如何在这个select语句中使用where条件呢？ 例如： EXECUTE format('SELECT count() FROM %I.%I WHERE some_column=user_def_variable ', _schema, _tb_name,column_name) - Karthik

@Karthik：我添加了一个答案。请记得提出问题作为问题。评论不是合适的地方。 - Erwin Brandstetter

Answer 2

似乎你想要在FORMAT中使用%I占位符，这样它才能将你的变量视为标识符。此外，INTO子句应该放在准备好的语句外面。

FOR tb_name in select table_name from information_schema.tables where table_schema='some_schema' and table_name like '%1%'
LOOP
  EXECUTE FORMAT('select count(*) from %I', tb_name) INTO tc;
END LOOP

- Andy Carlson

---

使用表名而不加模式限定符会导致一个隐蔽的错误，我添加了一些说明。 - Erwin Brandstetter