PostgreSQL 12+：如何将表中列的多行值传递给函数？

先解决当前错误 #

你遇到的错误是因为(select ARRAY[dokumnr] from source)返回了两行单元素数组，而函数需要的是一个包含所有值的单一数组。把ARRAY[dokumnr]换成array_agg(dokumnr)就能生成包含所有source列值的数组，修正后的调用语句：

select TestAddAssetTransactions( (select array_agg(dokumnr) from source) )

原函数逻辑无需修改，这样就能正常运行。

三种传递方式的对比与实现 #

1. 数组方式（你最初的思路） #

适合单列简单数据，语法简洁，处理少量数据效率不错。除了上面的修正，函数里的unnest用法可以简化：

-- 简化后的函数
CREATE OR REPLACE FUNCTION public.TestAddAssetTransactions(dokumnrs int[]) 
 RETURNS int AS
$BODY$
with i1 as (
INSERT INTO bilkaib (dokumnr)
select dokumnr from dok where dokumnr = any(dokumnrs) -- 用=any替代unnest子查询
returning *
)
select count(*) from i1;
$BODY$ language sql;

2. 自定义表类型（更适合结构化数据） #

如果以后需要传递多列数据，或者想让语义更清晰，推荐用自定义表类型：

-- 先创建表类型
create type int_dok_type as (dokumnr int);

-- 修改函数参数为表类型数组
CREATE OR REPLACE FUNCTION public.TestAddAssetTransactions(dokumnrs int_dok_type[]) 
 RETURNS int AS
$BODY$
with i1 as (
INSERT INTO bilkaib (dokumnr)
select t.dokumnr from dok, unnest(dokumnrs) t where dok.dokumnr = t.dokumnr
returning *
)
select count(*) from i1;
$BODY$ language sql;

-- 调用方式：用array_agg打包行数据
select TestAddAssetTransactions( (select array_agg(row(dokumnr)) from source) )

如果想更直接，还可以把函数参数设为setof int_dok_type（即行集合），调用时直接传入子查询：

CREATE OR REPLACE FUNCTION public.TestAddAssetTransactions(dokumnrs setof int_dok_type) 
 RETURNS int AS
$BODY$
with i1 as (
INSERT INTO bilkaib (dokumnr)
select t.dokumnr from dok, dokumnrs t where dok.dokumnr = t.dokumnr
returning *
)
select count(*) from i1;
$BODY$ language sql;

-- 调用更简洁
select TestAddAssetTransactions(select row(dokumnr) from source);

3. 临时表方式（适合超大量数据） #

如果要处理的数据量极大，临时表是个选择，但函数会依赖临时表的存在，耦合性较高：

-- 函数直接读取临时表
CREATE OR REPLACE FUNCTION public.TestAddAssetTransactions() 
 RETURNS int AS
$BODY$
with i1 as (
INSERT INTO bilkaib (dokumnr)
select dok.dokumnr from dok join source on dok.dokumnr = source.dokumnr
returning *
)
select count(*) from i1;
$BODY$ language sql;

-- 调用前确保source临时表存在（你已经创建并插入数据）
select TestAddAssetTransactions();

方案选择建议 #

• 单列少量数据：优先用数组方式，语法简单，无额外类型定义。
• 多列数据或需要清晰语义：用自定义表类型，扩展性好。
• 超大量数据：用临时表，避免数组内存限制。

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