CODEWITHSUNDEEP
sqlpostgresqlsql-insertplpgsql
Andrus
Andrus

Reputation: 27919

How to pass table column values to function

Table source contains integer column. Its values should be passed to Postgresql 12 function for selecting data from other table.

I tried to use array

    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 single column values from table rows to function? Should array, table type or temp table used?

Using Postgresql 12+

Upvotes: 0

Views: 77

Answers (1)

Zegarek
Zegarek

Reputation: 25943

You need to aggregate the ints in your function call. Otherwise you're just casting each value to a single-element array and they try to cast a column to an array of int.

select TestAddAssetTransactions( (select array_agg(dokumnr)::int[] from source) );

online demo

Upvotes: 1

Related Questions