How to pass table column values to function

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: Raw Message | Whole Thread | 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.

Responses

Browse pgsql-general by date

  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