On Mon, Jun 18, 2012 at 12:04 PM, utsav <utsav.pshah@xxxxxxx> wrote: > -- Table: bar > > -- DROP TABLE bar; > > CREATE TABLE bar > ( > barid integer, > barsubid integer, > barname text > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE bar > OWNER TO postgres; > -------------------------------------------------------------------------------------------- > -- Table: foo > > -- DROP TABLE foo; > > CREATE TABLE foo > ( > fooid integer, > foosubid integer, > fooname text > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE foo > OWNER TO postgres; > > -------------------------------------------------------------------------------------------- > > > -- Function: getallfoobar() > > -- DROP FUNCTION getallfoobar(); > > CREATE OR REPLACE FUNCTION getallfoobar3(foo OUT foo,bar OUT bar) > RETURNS SETOF record AS > $BODY$ > DECLARE > r foo%rowtype; > r1 bar%rowtype; > BEGIN > FOR r IN SELECT * FROM foo > WHERE fooid > 3 > LOOP > -- can do some processing here > RAISE NOTICE 'r == %',r; > -- return next row of SELECT > END LOOP; > > FOR r1 IN SELECT * FROM bar > WHERE barid > 0 > LOOP > -- can do some processing here > -- return next row of SELECT > RAISE NOTICE 'r1 == %',r1; > END LOOP; > RETURN NEXT; > END > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100 > ROWS 1000; > ----------------------------------------------------------------------------------- > > select * from getallfoobar3(); you're getting null results because you never assigned anything to your output variables. 'RETURN NEXT' will emit a new record for both OUT foo and OUT bar based on whatever they are containing at the time. Try running my example above and extending it. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
