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

2011/8/7 Ondrej Ivanič <ondrej.ivanic@gmail.com>:
> 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

You have a few of different methods for passing sets between functions.
1) refcursor as David noted.  reasonably fast. however, I find the
'FETCH' mechanic a little inflexible.
2) stage data to TABLE/TEMP TABLE; extremely flexible, but can be a
headache because a non temp table can get thrashed pretty hard a and a
'TEMP' can cause severe function plan invalidation issues if you're
not careful
3) arrays of composites -- the most flexible and very fast for *small*
amounts of records (say less than 10,000):

#3 is my favorite method unless the data being passed is very large.
Here is an example of it in use:

CREATE TYPE foo_t as (a int, b text);
CREATE FUNCTION get_foos() RETURNS SETOF foo_t AS
$$
BEGIN
  RETURN QUERY SELECT 1, 'abc' UNION ALL SELECT 2, 'def';
END;
$$ LANGUAGE PLPGSQL;

CREATE FUNCTION do_foos(_foos foo_t[]) returns VOID AS
$$
DECLARE
  f foo_t;
BEGIN
  FOR f in SELECT * FROM UNNEST(_foos)
  LOOP
    RAISE NOTICE '% %', f.a, f.b;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

postgres=# SELECT do_foos(ARRAY(SELECT (a,b)::foo_t FROM get_foos()));
NOTICE:  1 abc
NOTICE:  2 def
 do_foos
---------

(1 row)

Also, if you are deploying vs 9.1, be sure to check out Pavel's
for-in-array which is better method to do the unnest() which expands
the array.

merlin