我想写一个查询,统计数据库中的每个对象。我知道您可以查询统计信息,但我想知道如何编写这种通用类型的查询,以防我需要再次进行查询。
我得到了这个查询的结果,它给出了数据库中的每个表名:
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'
结果如下:
table_name
----------
queue_classic_jobs
activities
attachments
comments
csv_files
我想对每个结果行的每个表执行
count(*)
:table_name | row_count
----------------------------------
queue_classic_jobs 6
activities 2
attachments 4
comments 8
csv_files 10
然后对行计数列求和。我的查询应该是什么样的?
最佳答案
使用plpgsqlEXECUTE
命令。
你有两个选择。第一个是使用临时表保存结果的匿名代码块:
CREATE TEMP TABLE IF NOT EXISTS results(table_name text, row_count bigint);
TRUNCATE results;
DO $$
DECLARE
tname text;
BEGIN
FOR tname IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'
LOOP
EXECUTE format($fmt$
INSERT INTO results
SELECT '%s', count(*) from %s
$fmt$, tname, tname);
END LOOP;
END $$;
SELECT * FROM results
UNION ALL
SELECT 'TOTAL', sum(row_count) FROM results;
第二个选项是plpgsql函数:
CREATE OR REPLACE FUNCTION show_row_count()
RETURNS TABLE (table_name text, row_count bigint)
LANGUAGE plpgsql
AS $$
DECLARE
tname text;
BEGIN
FOR tname IN
SELECT i.table_name
FROM information_schema.tables i
WHERE table_schema='public'
AND table_type='BASE TABLE'
LOOP
RETURN QUERY EXECUTE format($fmt$
SELECT '%s'::text, count(*) from %s
$fmt$, tname, tname);
END LOOP;
END $$;
WITH row_counts AS (SELECT * FROM show_row_count())
SELECT * FROM row_counts
UNION ALL
SELECT 'TOTAL'::text, sum(row_count) FROM row_counts;
阅读更多:Executing Dynamic Commands