How to pass table column values to function

Em sáb., 11 de fev. de 2023 às 07:10, Andrus <kobruleht2@hot.ee> escreveu:

Hi!

Table source contains integer column. Its values should be passed to
function for selecting data from other table.

I tried

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 in (select * from
unnest(dokumnrs))
returning *
)

select count(*) from i1;
$BODY$ language sql;

create temp table bilkaib (dokumnr int ) on commit drop;
create temp table dok (dokumnr serial primary key ) on commit drop;
create temp table source (dokumnr int ) on commit drop;
insert into source values (1),(2);

select TestAddAssetTransactions( (select ARRAY[dokumnr] from
source)::int[] )

but got error

ERROR: more than one row returned by a subquery used as an expression

Probably you want an array_agg and not an array

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

On Sat, Feb 11, 2023 at 3:55 PM Andrus <kobruleht2@hot.ee> wrote:

Hi!

Table source contains integer column. Its values should be passed to
function for selecting data from other table.

I tried

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 in (select * from
unnest(dokumnrs))
returning *
)

select count(*) from i1;
$BODY$ language sql;

create temp table bilkaib (dokumnr int ) on commit drop;
create temp table dok (dokumnr serial primary key ) on commit drop;
create temp table source (dokumnr int ) on commit drop;
insert into source values (1),(2);

select TestAddAssetTransactions( (select ARRAY[dokumnr] from
source)::int[] )

but got error

ERROR: more than one row returned by a subquery used as an expression

How to pass set of integers to function? Should temp table with fixed name
used or is there better solution?

Using Postgresql 12+

Andrus.

try this:
select TestAddAssetTransactions((select array(select * from source)));