On Mon, Jun 18, 2012 at 1:33 PM, utsav <utsav.pshah@tcs.com> 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