我想写一个查询,统计数据库中的每个对象。我知道您可以查询统计信息,但我想知道如何编写这种通用类型的查询,以防我需要再次进行查询。
我得到了这个查询的结果,它给出了数据库中的每个表名:

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

10-05 23:07
查看更多