在Postgres 9.0+中使用PL/pgSQL在表上循环 [英] Loop on tables with PL/pgSQL in Postgres 9.0+
问题描述
我想遍历所有表以对每个表中的行进行计数.以下查询给我一个错误:
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$$;
错误:
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()
,但无济于事.
sql_features
is a table's name in my DB. I already tried to use quote_ident()
but to no avail.
推荐答案
游标返回记录,而不是标量值,因此"tablename"不是字符串变量.
The cursor returns a record, not a scalar value, so "tablename" is not a string variable.
串联将记录转换成类似于(sql_features)
的字符串.如果您选择了如果使用具有表名的模式名,则该记录的文本表示形式为(public,sql_features)
.
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语句:
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$$;
您可能要使用WHERE schemaname = 'public'
而不是not like 'pg_%'
来排除Postgres系统表.
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屋!