Search Postgresql Archives

Re: How to pass table column values to function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





Em sáb., 11 de fev. de 2023 às 07:10, Andrus <kobruleht2@xxxxxx> 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) ) 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux