PostgreSQL中调用含动态SQL的存储过程时，如何返回类似SELECT查询的表格式结果（对标BigQuery）

嘿，作为从BigQuery转PostgreSQL的过来人，我完全懂你这种别扭感——PostgreSQL的存储过程和BigQuery的逻辑确实不一样，尤其是返回结果这块，默认的record形式确实没法直接当表用。下面给你两种解决方案，优先推荐第一种，最贴合你想要的BigQuery体验：

方案一：改用函数（Function）替代存储过程（Procedure） #

PostgreSQL里，函数天生更适合返回查询结果集，能直接输出和SELECT * FROM xxx完全一致的表格式。我们可以把你原有的存储过程改成函数，调整一下返回逻辑：

create or replace function sbx.count_rows_from_table_func(
    pin_run_type varchar, 
    pin_filter varchar, 
    pin_table_name varchar
) returns table(num_rows bigint)
language plpgsql as
$$
declare 
    formatted_sql constant text := format(
        'select count(*) as num_rows from %s where %s', 
        pin_table_name, pin_filter
    );
begin
    if lower(pin_run_type) = 'execute' then
        -- 直接返回动态SQL的查询结果，和普通SELECT输出一致
        return query execute formatted_sql;
    else
        -- 如果是需要返回生成的SQL文本，这里可以调整逻辑，比如返回通知或者修改返回类型
        raise notice 'Generated SQL: %', formatted_sql;
        -- 也可以改成返回SQL文本，只需把函数返回类型改成text，然后用return formatted_sql;
        return query select null::bigint; -- 这里返回空行作为占位，可按需调整
    end if;
exception when others then
    raise notice 'Error Message: %', SQLERRM;
    return;
end
$$;

调用的时候直接像查普通表一样：

select * from sbx.count_rows_from_table_func('execute', 'id > 10', 'sbx.my_table');

这样返回的结果就是标准的表格式，和你在BigQuery里得到的完全一样。

方案二：用存储过程+游标（Refcursor）返回结果（不推荐，步骤繁琐） #

如果你一定要用存储过程，也可以通过游标来返回结果集，但需要手动处理游标，步骤比函数麻烦很多：

create or replace procedure sbx.count_rows_from_table_prc(
    pin_run_type varchar, 
    pin_filter varchar, 
    pin_table_name varchar, 
    out result_cursor refcursor
) language plpgsql as
$$
declare 
    formatted_sql constant text := format(
        'select count(*) as num_rows from %s where %s', 
        pin_table_name, pin_filter
    );
begin
    if lower(pin_run_type) = 'execute' then
        -- 打开游标指向动态SQL的结果
        open result_cursor for execute formatted_sql;
    else
        raise notice 'Generated SQL: %', formatted_sql;
        -- 如果要返回SQL文本，也可以用游标返回
        open result_cursor for select formatted_sql as generated_sql;
    end if;
exception when others then
    raise notice 'Error Message: %', SQLERRM;
end
$$;

调用时需要在事务里操作游标：

begin;
call sbx.count_rows_from_table_prc('execute', 'id > 10', 'sbx.my_table', 'my_result_cursor');
fetch all from my_result_cursor;
commit;

总结 #

• 优先选函数方案，这是PostgreSQL中返回查询结果最直接、最贴合BigQuery使用习惯的方式。
• 存储过程更适合做数据修改、事务操作这类“动作型”任务，返回结果集不是它的强项。

备注：内容来源于stack exchange，提问作者SHIVAM SINGH