On Aug 7, 2011, at 23:24, Ondrej Ivanič <ondrej.iva...@gmail.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.iva...@gmail.com) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
