Re: RETURNING MORE THAN ONE CUSTOM TYPE FROM FUNCTION

On Thu, Jun 14, 2012 at 1:10 AM, utsav <utsav(dot)pshah(at)tcs(dot)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