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
2) pass query as text parameter and open no scroll cursor inside the function
It works but it's ugly.
3) hardcode the query inside function
Similar to (2) and looks better but I need several functions with
different queries inside:
...
for r in (query) loop
...
end loop;
...
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]
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.ivanic@gmail.com)