Re: Passing result of multirow subquery to C function

On Tue, Dec 31, 2013 at 10:24 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> Select hello(name) from names
>
> You can only pass a single row to a function. A work around is to use
> arrays but for your example the above form is what you would do and is
> typical of function usage in a table context.

for posterity, you could do arrays with,

SELECT hello(array(select name from names));

if 'name' is text, the function would take TEXT[]. you could also pass
the entire table via:

SELECT hello(array(select n from names n));

in which case the hello function would take names[] and you could pass
through the data in the receiving function with FOREACH or unnest().
This type of invocation is OK, I use it often, but will not scale very
well to large amounts of data passed in to the function. If you need
industrial passing handling between functions TEMP tables (perhaps
decorated with ON COMMIT DROP) are probably the best way to go but
require some extra care to manage scope and will be slower for the
'just a few records' case.

Yet another way to do it is with refcursors which are basically
cursors you can refer to by string. If you do use the convention:

SELECT func(col) FROM foo;

I would advise making func() an IMMUTABLE plpgsql function or (even
better) a sql function if it supports inlining.

merlin