Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

On Thu, Jun 14, 2012 at 1:10 AM, utsav <utsav.pshah@tcs.com> wrote:
> I am doing Oracle to PostgreSQL migration activity as part of Procedure
> Migration in Oracle there are *OUT parameters which return records(using
> bulk collect) of custom type.*
>
> *like function returing type1,type2. *
>
> What will be alternative for PostgreSQL to do this.
>
> *There are OUT parameters in PostgreSQL but i am not able to set returns set
> of type1,type2 .
> *
> Appreciate your Help.

postgres=# create type foo as (a int, b text);
CREATE TYPE
postgres=# create type bar as (c int, d text);
CREATE TYPE
postgres=# create function f(foo out foo, bar out bar) returns setof
record as $$
  select (v, v::text)::foo, (v, v::text)::bar from generate_series(1,3) v;
$$ language sql;
CREATE FUNCTION
postgres=# select f();
         f
-------------------
 ("(1,1)","(1,1)")
 ("(2,2)","(2,2)")
 ("(3,3)","(3,3)")
(3 rows)

postgres=# select * from f();
  foo  |  bar
-------+-------
 (1,1) | (1,1)
 (2,2) | (2,2)
 (3,3) | (3,3)
(3 rows)

postgres=# select (foo).*, (bar).* from f();
 a | b | c | d
---+---+---+---
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2
 3 | 3 | 3 | 3
(3 rows)

merlin