postgresql - PL/pgSQL函数中的动态SELECT INTO

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

假设我有一个名为tb_name的变量，该变量从FOR填充到information_schema.tables循环中。现在，我有一个名为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的数据类型应该是什么？

最佳答案

CREATE OR REPLACE FUNCTION myfunc(_tbl_pattern text, _schema text = 'public')
  RETURNS void AS  -- or whatever you want to return
$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$  LANGUAGE plpgsql;

笔记

我在所有参数和变量前加了下划线(_)，以避免命名与表列的冲突。只是一个有用的约定。

_tc应该是 bigint ，因为这是合计函数count()返回的内容。

_tb_name的数据类型是动态地从其父列派生的:information_schema.tables.table_name %TYPE 。请参阅Copying Types in the manual一章。

您确定只需要information_schema.tables中列出的表吗？有道理，但要注意含义。看:

How to check if a table exists in a given schema

a_horse已经pointed to the manual和Andy provided a code example。这就是将分配给从动态查询(带有EXECUTE的查询)返回到(行)变量的单个行或值的方式。单个列(如示例中的count)会自动从行类型分解，因此我们可以直接将其分配给标量变量tc，这与将整行分配给记录或行变量的方式相同。有关的:

How to get the value of a dynamically generated field name in PL/pgSQL

对动态查询中的表名进行Schemat限定。当前search_path中可能还有其他同名的表，这将导致完全错误的结果(并且非常令人困惑!)，而无需进行模式限定。鬼bug的 bug !或此模式根本不在search_path中，这会使该函数立即引发异常。

How does the search_path influence identifier resolution and the "current schema"

始终正确使用引用标识符可以防止SQL注入(inject)和随机错误。模式和表必须分别引用!看:

Table name as a PostgreSQL function parameter

Truncating all tables in a Postgres database

我使用~中的正则表达式运算符table_name ~ _tbl_pattern 代替了table_name LIKE ('%' || _tbl_pattern || '%')，这更为简单。无论哪种方式，请注意pattern参数中的特殊字符!看:

PostgreSQL Reverse LIKE

Escape function for regular expression or LIKE patterns

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

我在函数调用_schema text = 'public'中为模式名称设置了默认值。只是为了方便起见，您可能想要也可能不想要。看:

Assigning default value for type

解决your comment:要传递值，请使用USING子句，例如:

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

有关的:

INSERT with dynamic table name in trigger function

关于postgresql - PL/pgSQL函数中的动态SELECT INTO，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45998984/