From: | Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to pass table column values to function |
Date: | 2023-02-11 12:21:03 |
Message-ID: | CAB-JLwZM-+AiVoK7_U3NVwLr3Ois+Mgw4yMGFw4-vvwp011_iQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Em sáb., 11 de fev. de 2023 às 07:10, Andrus <kobruleht2(at)hot(dot)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) )
From | Date | Subject | |
---|---|---|---|
Next Message | jian he | 2023-02-11 14:16:40 | Re: How to pass table column values to function |
Previous Message | Andrus | 2023-02-11 10:10:25 | How to pass table column values to function |