环路与PL / pgSQL用在Postgres表9.0或更高版本(Loop on tables wi

我想通过我的所有表圈在他们每个人的计算行。下面的查询得到我的错误：

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

错误：

 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()但无济于事。

Answer 1:

光标返回的记录，不是标值，所以“表名”不是一个字符串变量。

级联变成记录到，看起来像这样的字符串(sql_features) 如果你已经如与表名的SCHEMANAME选择，记录的文本表示本来(public,sql_features)

所以，你需要访问记录中列创建你的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$$;

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

Answer 2:

我不记得我真正需要使用显式游标在PLPGSQL循环的最后一次。
使用的隐式游标FOR循环，这是干净多了：

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 ）。

此外，您真正需要使用quote_ident()或format()用%I ，以防止SQL注入。一个表名可以是双引号内几乎所有的东西 。

小细节：逃脱下划线（ _中） LIKE模式，使其文字下划线： 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
$$;

查询pg_catalog.pg_class而不是tablename ，它提供了表的OID。
的对象标识符类型regclass是很方便的，以简化，特别是，表名是双引号和模式限定在必要时自动地（也防止SQL注入）。
此查询还排除了临时表（临时架构命名为pg_temp%内）。

如果你想从一个给定的模式只表：

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

文章来源: Loop on tables with PL/pgSQL in Postgres 9.0+