Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

On Mon, Jun 18, 2012 at 1:33 PM, utsav <utsav(dot)pshah(at)tcs(dot)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