postgresql 在 Postgres 9.0+ 中使用 PL/pgSQL 循环表

Loop on tables with PL/pgSQL in Postgres 9.0+

提问by Totor

I want to loop through all my tables to count rows in each of them. The following query gets me an error:

我想遍历我所有的表来计算每个表中的行数。以下查询给我一个错误：

DO $$
DECLARE
    tables CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tablename NOT LIKE 'pg_%'
        ORDER BY tablename;
    tablename varchar(100);
    nbRow int;
BEGIN
    FOR tablename IN tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || tablename INTO nbRow;
        -- Do something with nbRow
    END LOOP;
END$$;

Errors:

错误：

ERROR:  syntax error at or near ")"
LINE 1: SELECT count(*) FROM (sql_features)
                                          ^
QUERY:  SELECT count(*) FROM (sql_features)
CONTEXT:  PL/pgSQL function inline_code_block line 8 at EXECUTE statement

ERROR:  syntax error at or near ")"
LINE 1: SELECT count(*) FROM (sql_features)
                                          ^
QUERY:  SELECT count(*) FROM (sql_features)
CONTEXT:  PL/pgSQL function inline_code_block line 8 at EXECUTE statement

sql_featuresis a table's name in my DB. I already tried to use quote_ident()but to no avail.

sql_features是我数据库中的表名。我已经尝试使用quote_ident()但无济于事。

回答by Erwin Brandstetter

I can't remember the last time I actually needed to use an explicit cursor for looping in plpgsql.
Use the implicit cursor of a FORloop, that's much cleaner:

我不记得上次我实际上需要使用显式游标在 plpgsql 中进行循环是什么时候。
使用FORloop的隐式游标，这更干净：

DO
$$
DECLARE
    rec   record;
    nbrow bigint;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      EXECUTE 'SELECT count(*) FROM '
        || quote_ident(rec.schemaname) || '.'
        || quote_ident(rec.tablename)
      INTO nbrow;
      -- Do something with nbrow
   END LOOP;
END
$$;

You need to include the schema name to make this work for all schemas (including those not in your search_path).

您需要包含架构名称以使其适用于所有架构（包括不在您的 中的架构search_path）。

Also, you actually needto use quote_ident()or format()with %Ito safeguard against SQL injection. A table name can be almost anythinginside double quotes.

此外，您实际上需要使用quote_ident()或format()with%I来防止 SQL 注入。表名几乎可以是双引号内的任何内容。

Minor detail: escape the underscore (_) in the LIKEpattern to make it a literalunderscore: tablename NOT LIKE 'pg\_%'

小细节：_将LIKE模式中的下划线 ( )转义为文字下划线：tablename NOT LIKE 'pg\_%'

How I might do it:

我可以怎么做：

DO
$$
DECLARE
    tbl   regclass;
    nbrow bigint;
BEGIN
   FOR tbl IN
      SELECT c.oid
      FROM   pg_class     c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relkind = 'r'
      AND    n.nspname NOT LIKE 'pg\_%'         -- system schema(s)
      AND    n.nspname <> 'information_schema'  -- information schema
      ORDER  BY n.nspname, c.relname
   LOOP
      EXECUTE 'SELECT count(*) FROM ' || tbl INTO nbrow;
      -- raise notice '%: % rows', tbl, nbrow;
   END LOOP;
END
$$;

• Query pg_catalog.pg_classinstead of tablename, it provides the OID of the table.

• The object identifier type regclassis handy to simplify, in particular, table names are double-quoted and schema-qualified where necessary automatically (also prevents SQL injection).

• This query also excludes temporary tables (temp schema is named with pg_temp%internally).

• If you want only tables from a given schema:

  AND    n.nspname = 'public' -- schema name here, case-sensitive
  

• Querypg_catalog.pg_class而不是tablename，它提供表的 OID。

• 的对象标识符类型regclass是很方便的，以简化，特别是，表名是双引号和模式限定在必要时自动地（也防止SQL注入）。

• 此查询还排除临时表（临时模式在pg_temp%内部命名）。

• 如果您只需要给定架构中的表：

  AND    n.nspname = 'public' -- schema name here, case-sensitive
  

回答by a_horse_with_no_name

The cursor returns a record, not a scalar value, so "tablename" is not a string variable.

游标返回一个记录，而不是一个标量值，所以“tablename”不是一个字符串变量。

The concatenation turns the record into a string that looks like this (sql_features). If you had selected e.g. the schemaname with the tablename, the text representation of the record would have been (public,sql_features).

串联将记录转换为如下所示的字符串(sql_features)。如果您选择了例如带有表名的模式名，则记录的文本表示将是(public,sql_features).

So you need to access the column inside the record to create your SQL statement:

所以你需要访问记录内的列来创建你的 SQL 语句：

DO $$
DECLARE
    tables CURSOR FOR
        SELECT tablename
        FROM pg_tables
        WHERE tablename NOT LIKE 'pg_%'
        ORDER BY tablename;
    nbRow int;
BEGIN
    FOR table_record IN tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || table_record.tablename INTO nbRow;
        -- Do something with nbRow
    END LOOP;
END$$;

You might want to use WHERE schemaname = 'public'instead of not like 'pg_%'to exclude the Postgres system tables.

您可能希望使用WHERE schemaname = 'public'而不是not like 'pg_%'排除 Postgres 系统表。