PostgreSQL存储过程如何传入param1、param2等任意N个多值参数

原来的存储过程存在两个核心问题：

• wellid是单文本类型，无法承载多个井号值
• WHERE "Well ID" in (wellid)只会将整个wellid字符串作为单个值匹配，无法解析多值

方案1：使用数组类型作为入参（推荐） #

直接将参数类型修改为文本数组，用ANY运算符匹配数组元素，兼容性最好、性能最高。

修改后的存储过程代码 #

create or replace procedure aoi(
wellid text[], -- 改为文本数组类型
INOUT cumoil numeric,INOUT cumgas numeric) 
AS $BODY$
begin
-- 如果需要统计所有传入井号的总油气产量，删掉GROUP BY子句，否则多井返回多行会导致INTO赋值报错
SELECT SUM(cast(a.oil as decimal)) as cumoil,
SUM(cast(a.gas as decimal)) as cumgas
into cumoil,cumgas
from XYZ a where "Well ID" = ANY(wellid)
-- 如果需要按单井分组返回，要修改返回结构为结果集，不能用固定INOUT参数
-- GROUP BY a."Well ID";
commit;
end;$BODY$
LANGUAGE plpgsql; -- 补充声明语言，避免执行报错

调用示例 #

-- 传入多个井号值
call aoi(ARRAY['well1','well2','well3'], 0, 0);

方案2：保持参数为文本类型，传入逗号分隔字符串 #

如果不能修改参数类型，可以传入用逗号拼接的井号字符串，在存储过程内拆分后匹配。

修改后的存储过程代码 #

create or replace procedure aoi(
wellid text, -- 保持原文本类型，传入格式如'well1,well2,well3'
INOUT cumoil numeric,INOUT cumgas numeric) 
AS $BODY$
begin
SELECT SUM(cast(a.oil as decimal)) as cumoil,
SUM(cast(a.gas as decimal)) as cumgas
into cumoil,cumgas
from XYZ a where "Well ID" in (
    -- 将逗号分隔的字符串拆分为多行值
    select unnest(string_to_array(wellid, ','))
);
commit;
end;$BODY$
LANGUAGE plpgsql;

调用示例 #

call aoi('well1,well2,well3', 0, 0);

注意：如果需要返回每个井的单独油气产量，需要将存储过程的返回值修改为SETOF RECORD类型返回结果集，原固定INOUT参数仅支持返回单条统计结果。 #

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