Merlin, Thank you for that explanation of passing arrays to a function
- Gerald
On 1/2/2014 12:33 PM, Merlin Moncure wrote:
> On Tue, Dec 31, 2013 at 10:24 AM, David Johnston <polobo@yahoo.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
>
>
--
siamo arrivati sani e salvi