On Mon, Jun 18, 2012 at 1:33 PM, utsav <utsav.pshah@xxxxxxx> wrote: > -- 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' > getallfoobar3.foo = r; > 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; > getallfoobar3.bar = r1; > RETURN NEXT; > END > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100 > ROWS 1000; > > > /Thanks for your help ../ > > *But still i want output in record here i am getting only last record in > ouput ...* sure -- you're only calling one 'return next'. you need to call return next for each row you want to return. you've also got two loops -- that isn't going to work as intended. your code should be structured like this: FOR <something that gets same sized list of foo and bar> LOOP <get a foo into f> foo := f; <get a bar into b> bar := b; RETURN NEXT; END LOOP; If you want heterogeneously sized lists to be returned from a single function, you might want to consider returning arrays, not a set returning function. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
