Re: [GENERAL] Sending Results From One Function As Input into Another Function
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
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
Re: [GENERAL] Sending Results From One Function As Input into Another Function
On Mon, Sep 26, 2011 at 6:49 AM, Albe Laurenz laurenz.a...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
