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