From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | utsav <utsav(dot)pshah(at)tcs(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION |
Date: | 2012-06-18 17:25:04 |
Message-ID: | CAHyXU0ybnSUt1coycnmAE0iUkyRUuOa7LLRCbCjf9Ty74x6d3A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | utsav | 2012-06-18 18:33:40 | Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION |
Previous Message | utsav | 2012-06-18 17:05:38 | Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION |