Re: Sending Results From One Function As Input into Another Function

On Mon, Sep 26, 2011 at 6:49 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> Jeff Adams wrote:
>> I need to send the results (SETOF RECORDS) from one function into
> another
>> function, to produce another result (SETOF RECORDS). I am not quite
> sure how
>> to do get this done. The first function filters a large table down a
> more
>> manageable dataset. I want to send the results of this first function
> to
>> another function, where computations are performed. I could combine
> into a
>> single function, but I would lose some flexibility that I would like
> to
>> maintain by keeping the two functions separate. Preliminary research
>> suggests that cursors might be the way to go, but I am not too
> experienced
>> with the use of cursors and was unable to find good examples. Any help
> would
>> be greatly appreciated...
>
> Here's an example:
>
> SELECT * FROM test;
>
>  id |  val
> ----+-------
>  1 | one
>  2 | two
>  3 | three
>  4 | four
> (4 rows)
>
> CREATE FUNCTION filter() RETURNS refcursor
>   LANGUAGE plpgsql STABLE STRICT AS
> $$DECLARE
>   /* assignment gives the cursor a name */
>   curs refcursor := 'curs';
> BEGIN
>   OPEN curs FOR
>      SELECT id, val FROM test WHERE id%2=0;
>   RETURN curs;
> END;$$;
>
> CREATE FUNCTION compute(curs refcursor) RETURNS text
>   LANGUAGE plpgsql STABLE STRICT AS
> $$DECLARE
>   v test;  -- row type for table
>   r text := '';
> BEGIN
>   LOOP
>      FETCH curs INTO v;
>      EXIT WHEN v IS NULL;
>      r := r || v.val;
>   END LOOP;
>   RETURN r;
> END;$$;
>
> SELECT compute(filter());
>
>  compute
> ---------
>  twofour
> (1 row)

Another method of doing this which I like to point out is via arrays
of composite types. It's suitable when the passed sets are relatively
small (say less than 10k) and is more flexible -- forcing all data
manipulation through FETCH is (let's be frank) pretty awkward and with
some clever work you can also involve the client application in a more
regular way. You can use an implict table type or a specially defined
composite type to convey the data:

create type t as (a int, b text, c timestamptz);

create function filter() returns t[] as
$$
select array(select row(a,b,c)::t from foo);
$$ language sql;

create function do_stuff(_ts t[]) returns void as
$$
declare
_t t;
begin
foreach _t in array _ts
loop
raise notice '%', _t;
end loop;
end;
$$ language plpgsql;

note: foreach in array feature is new to 9.1 -- 8.4+ use unnest() --
before that you have to hand roll unnest().

merlin