PostgreSQL带输出参数的动态存储过程调用问题

问题核心：动态调用存储过程时，输出参数默认以IN模式传递，导致存储过程内部的赋值无法回传到外部变量。

修正方案 #

1. 显式指定输出参数的传递模式为OUT，确保赋值能回传
2. 使用format函数拼接动态SQL，避免SQL注入风险

修正后的代码 #

drop schema test cascade;
create schema test;

CREATE OR REPLACE PROCEDURE test.Test(p_in IN TEXT, p_out OUT TEXT) AS
$body$
DECLARE
BEGIN
    RAISE NOTICE '--->  %', p_in;
    p_out := p_in;
    RAISE NOTICE '--->  %', p_out;
END;
$body$
    LANGUAGE PLPGSQL
;

CREATE OR REPLACE PROCEDURE test.Callout(p_routine IN TEXT, p_in IN TEXT, p_out OUT TEXT) AS
$body$
DECLARE
v_stmt TEXT;
BEGIN
    RAISE NOTICE '->  %', p_in;
    -- 用format安全拼接存储过程调用语句
    v_stmt := format('CALL %I($1, $2)', p_routine);
    -- 显式指定参数传递模式
    EXECUTE v_stmt USING IN p_in, OUT p_out;
    RAISE NOTICE '->  %', p_out;
END;
$body$
    LANGUAGE PLPGSQL
;

DO
$body$
DECLARE
    x    TEXT;
    y    TEXT;
BEGIN
    x := 'HALLO';
    CALL test.Callout(p_routine => 'test.TEST', p_in => x, p_out => y);
    RAISE NOTICE '> %', y;
END;
$body$
    LANGUAGE PLPGSQL
;

关键说明 #

• format('CALL %I($1, $2)', p_routine)：通过%I格式化标识符，避免存储过程名称含特殊字符或引发SQL注入
• USING IN p_in, OUT p_out：明确标记参数模式，让p_out作为输出参数接收存储过程内部的赋值，确保值能回传到test.Callout的变量中
• 执行后最终会输出HALLO，说明输出参数已正确传递

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