Re: How to pass table column values to function

[jian he]

On Sat, Feb 11, 2023 at 3:55 PM Andrus <kobrule...@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)));