如何将Shell调用Oracle存储过程改为调用PostgreSQL存储过程？

问题说明 #

需将调用Oracle存储过程的Shell脚本适配为PostgreSQL版本，其中v_err_code（整数类型）和v_err_msg（文本类型）为存储过程的OUT参数，需兼容PostgreSQL的psql客户端调用逻辑。

原Oracle Shell调用代码 #

load_result=`${ORACLE_HOME}/bin/sqlplus -silent $CONNECT_STRING <<EOF

set heading off
set feedback off

variable v_err_code      number;
variable v_err_msg       varchar2(4000)

execute testuser.test_pkg.MAIN('$P_param1', :v_err_code, :v_err_msg);

print v_err_code
print v_err_msg

exit;
EOF`

echo "load_result= $load_result"

尝试的PostgreSQL Shell调用代码（存在问题） #

PostgreSQL的psql不支持Oracle SQL*Plus的var和print命令，因此以下代码无法正常运行：

load_result=$(psql -d ${PG_DB} -c "
   var v_err_code    INTEGER;
   var v_err_msg     TEXT;

   CALL testuser.test_pkg.MAIN('$P_param1', :v_err_code, :v_err_msg);

   print v_err_code
   print v_err_msg

   exit;
");

echo "load_result= $load_result"

修正后的PostgreSQL Shell调用代码 #

通过PL/pgSQL匿名块捕获OUT参数并输出，再在Shell中提取结果：

# 调用PostgreSQL存储过程并获取OUT参数
load_result=$(psql -d ${PG_DB} -t -A -c "
DO \$\$
DECLARE
    v_err_code INTEGER;
    v_err_msg TEXT;
BEGIN
    -- 调用存储过程，传入输入参数并接收OUT参数
    CALL testuser.test_pkg.MAIN('$P_param1', v_err_code, v_err_msg);
    -- 用RAISE NOTICE输出参数值，供Shell捕获
    RAISE NOTICE '%', v_err_code;
    RAISE NOTICE '%', v_err_msg;
END \$\$;
" 2>&1 | grep -E 'NOTICE:' | sed 's/NOTICE: //g')

# 输出最终结果
echo "load_result= $load_result"

关键说明 #

1. 替代var/print：用PL/pgSQL的DECLARE声明变量接收OUT参数，通过RAISE NOTICE输出参数值
2. Shell结果处理：psql的NOTICE输出到标准错误，通过2>&1重定向到标准输出后，用grep和sed提取纯参数内容
3. 格式优化：-t关闭表头、-A取消列对齐，避免多余输出干扰结果提取

可选方案（存储过程改为返回结果集） #

若存储过程可调整为返回结果集（或改为函数），可直接用SELECT调用提取结果：

load_result=$(psql -d ${PG_DB} -t -A -c "
SELECT * FROM testuser.test_pkg.MAIN('$P_param1');
")

echo "load_result= $load_result"

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