PostgreSQL循环执行查询并返回结果行的实现方法

你当前使用的DO匿名块仅能执行逻辑、输出日志，无法直接返回结果集。要实现返回包含Schema名称和计数的结果行，需要创建一个返回表类型的自定义函数，具体实现如下：

步骤1：创建返回表的函数 #

CREATE OR REPLACE FUNCTION count_adaptor_records()
RETURNS TABLE(schema_name varchar, record_count bigint)
LANGUAGE plpgsql
AS $$
DECLARE
    _schema text;
    _count bigint;
BEGIN
    -- 遍历public.environment中的所有schema名称
    FOR _schema IN SELECT display_name FROM public.environment LOOP
        -- 动态执行统计查询，将结果存入变量
        EXECUTE format(
            'SELECT count(*) FROM %I.adaptor WHERE is_deleted = false',
            _schema
        ) INTO _count;
        
        -- 将当前schema和计数作为一行加入结果集
        RETURN NEXT (_schema, _count);
    END LOOP;
END;
$$;

步骤2：调用函数获取结果 #

直接执行函数即可得到结构化的结果：

SELECT * FROM count_adaptor_records();

步骤3：在Python的psycopg2中调用 #

你可以像执行普通SELECT语句一样调用这个函数，用fetchall()或fetchone()获取结果：

import psycopg2

conn = psycopg2.connect("your_connection_string")
cur = conn.cursor()

cur.execute("SELECT * FROM count_adaptor_records();")
results = cur.fetchall()  # 获取所有结果行，每个元素是(schema_name, record_count)

# 遍历输出示例
for schema, cnt in results:
    print(f"Schema {schema}: {cnt} records")

cur.close()
conn.close()

关键说明 #

• RETURNS TABLE(...)：定义函数返回的表结构，指定列名和对应数据类型
• RETURN NEXT：将每一组_schema和_count作为一行添加到结果集中
• format()的%I：自动转义标识符（Schema/表名），避免SQL注入风险，同时兼容包含特殊字符的名称

内容的提问来源于stack exchange，提问作者Atul Phirke