在 Postgres 9.0+ 中使用 PL/pgSQL 循环表 [英] Loop on tables with PL/pgSQL in Postgres 9.0+

问题描述

我想遍历我所有的表来计算每个表中的行数.以下查询让我出错:

做$$宣布表 CURSOR FOR从 pg_tables 中选择表名WHERE tablename NOT LIKE 'pg_%'ORDER BY 表名；表名 varchar(100);nbRow int;开始FOR 表名 IN 表 LOOPEXECUTE 'SELECT count(*) FROM ' ||表名 INTO nbRow;-- 用 nbRow 做点什么结束循环；结束$$;

错误:

<块引用>

错误:)"处或附近的语法错误第 1 行:SELECT count(*) FROM (sql_features)^查询:选择计数(*)从(sql_features)上下文:PL/pgSQL 函数 inline_code_block 第 8 行在 EXECUTE 语句

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

解决方案

游标返回的是记录，而不是标量值，因此tablename"不是字符串变量.

串联将记录转换成一个字符串，看起来像这样(sql_features).如果您选择了例如带有表名的模式名，记录的文本表示将是 (public,sql_features).

所以你需要访问记录里面的列来创建你的 SQL 语句:

做$$宣布表 CURSOR FOR选择表名从 pg_tablesWHERE tablename NOT LIKE 'pg_%'ORDER BY 表名；nbRow int;开始FOR table_record IN 表 LOOPEXECUTE 'SELECT count(*) FROM ' ||table_record.tablename INTO nbRow;-- 用 nbRow 做点什么结束循环；结束$$;

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

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

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

解决方案

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

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).

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

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.

这篇关于在 Postgres 9.0+ 中使用 PL/pgSQL 循环表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！