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

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

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

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

我不记得上次在 PL/pgSQL 中实际需要使用显式游标进行循环是什么时候了。
使用 a 的隐式游标FOR loop https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING，这样就干净多了：

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
$$;

您需要包含模式名称才能使其适用于所有模式（包括那些不在您的模式中的模式）search_path).

还有，你其实need to use quote_ident() https://www.postgresql.org/docs/current/functions-string.html or format() https://www.postgresql.org/docs/current/functions-string.html with %I or a regclass https://www.postgresql.org/docs/current/datatype-oid.html变量来防止 SQL 注入。表名可以是几乎所有东西双引号内。看：

• 表名作为 PostgreSQL 函数参数 https://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349

小细节：转义下划线 (_） 在里面LIKE模式，使其成为literal下划线：tablename NOT LIKE 'pg\_%'

我该怎么做：

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_class https://www.postgresql.org/docs/current/catalog-pg-class.html代替tablename，它提供表的 OID。

The 对象标识符类型regclass https://www.postgresql.org/docs/current/datatype-oid.html简化起来很方便。特别是，表名在必要时自动用双引号和模式限定（也防止SQL注入 https://stackoverflow.com/a/10711349/939860).

此查询还排除临时表（临时架构名为pg_temp%内部）。

仅包含给定架构中的表：

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