Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

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