Thread: Sending Results From One Function As Input into Another Function
Greetings, 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... Jeff
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
On Mon, Sep 26, 2011 at 6:49 AM, Albe Laurenz <laurenz.albe@wien.gv.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
Thanks for the response Laurenz. I will give it a go... Jeff -----Original Message----- From: Albe Laurenz [mailto:laurenz.albe@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