Re: How to pass table column values to function

jian he <jian.universality@xxxxxxxxx> · Sat, 11 Feb 2023 20:01:40 +0545

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