Thanks for the response Laurenz. I will give it a go... Jeff
-----Original Message----- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, September 26, 2011 7:50 AM To: Jeff Adams *EXTERN*; pgsql-general@postgresql.org Subject: RE: [GENERAL] Sending Results From One Function As Input into Another Function 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) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
