| From: | Andrus <kobruleht2(at)hot(dot)ee> |
|---|---|
| To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
| Subject: | How to pass table column values to function |
| Date: | 2023-02-11 10:10:25 |
| Message-ID: | 438cee94-b9f2-6626-e38a-dd33831af718@hot.ee |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Marcos Pegoraro | 2023-02-11 12:21:03 | Re: How to pass table column values to function |
| Previous Message | Jon Erdman | 2023-02-11 03:08:59 | Re: valgrind a background worker |