Re: table / query as a prameter for PL/pgSQL function

On Aug 7, 2011, at 23:24, Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> wrote:

> Hi,
>
> It is possible to pass query result (or cursor?) as function
> parameter? I need a function which emits zero or more rows per input
> row (map function from map&reduce paradigm). Function returns record
> (or array): (value1, value2, value3)
> I've tried the following:
>
> 1) create or replace function test (r record) returns setof record as $$ ...
> Doesn't work: PL/pgSQL functions cannot accept type record
>

From the docs you can try using the "refcursor" data type though I have never done so myself.
>
>
> 4) use function in "select" clause:
> select my_map_func(col1, col2, col3, col4) from ... -- the rest of the query
> In this case I wasn't able figure out how to access record members
> returned by the function:
>
> select ?, ?, ?, count(*) from (
> select my_map_func(col1, col2, col3, col4) as map_func_result from ...
> ) as map
> group by 1, 2, 3
>
> The '?' should be something like map.map_func_result.value1 (both
> map.value1 and map_func_result.value1 doesn't not work). If function
> returns array then I can access value1 by using map_func_result[1]
>

Try " (map.map_func_result).value1 " - the parenthesis around the table alias and column are necessary.

> Is there a better way how to solve this? I'm kind of satisfied with 4
> (maybe 3) but it is little bit cumbersome
>
> Thanks,
> --
> Ondrej Ivanic
> (ondrej(dot)ivanic(at)gmail(dot)com)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general