Thread

1. How to pass table column values to function

   Andrus <kobruleht2@hot.ee> — 2023-02-11T10:10:25Z

   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.
   

2. Re: How to pass table column values to function

   Marcos Pegoraro <marcos@f10.com.br> — 2023-02-11T12:21:03Z

   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) )
   

3. Re: How to pass table column values to function

   jian he <jian.universality@gmail.com> — 2023-02-11T14:16:40Z

   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)));