Re: (SETOF) RECORD AS complex_type

On Mon, Jan 08, 2007 at 05:25:17PM +0200, Hannu Krosing wrote:
> Ühel kenal päeval, K, 2006-12-27 kell 14:06, kirjutas David Fetter:
> > Folks,
> >
> > While using DBI-Link, I've noticed a little lacuna in how functions
> > returning (SETOF) RECORD work, namely, that you have to cast them to
> > explicit lists of columns, even when that list of columns corresponds
> > to an existing complex type.
> >
> > What would be involved in fixing the casting operation so that the
> > following would work?
> >
> > CREATE TYPE foo AS (
> > a INT4,
> > b INT8,
> > c POINT,
> > d TEXT
> > );
> >
> > CREATE FUNCTION bar(output_type TEXT)
> > RETURNS SETOF RECORD
> > ...
> >
> > SELECT * FROM bar('foo') AS foo;
> >
> > Cheers,
> > D
>
> using OUT parameters works nice for me
>
> hannu=# CREATE FUNCTION bar(IN cnt INT, OUT a INT4, OUT b INT8, OUT c
> POINT, OUT d TEXT)
> hannu-# RETURNS SETOF RECORD
> hannu-# LANGUAGE SQL
> hannu-# AS $$
> hannu$# SELECT '1'::INT4,'1'::INT8,'(1,1)'::POINT,'text'::TEXT FROM
> generate_series(1,3);

You're assuming here that you know at function creation time what the
structure of the returning rowset will be. In the case of DBI-Link, I
don't.

Cheers,
D
> hannu$# $$;
> CREATE FUNCTION
> hannu=# select * from bar(1);
> a | b | c | d
> ---+---+-------+------
> 1 | 1 | (1,1) | text
> 1 | 1 | (1,1) | text
> 1 | 1 | (1,1) | text
> (3 rows)
>
>
>
> --
> ----------------
> Hannu Krosing
> Database Architect
> Skype Technologies OÜ
> Akadeemia tee 21 F, Tallinn, 12618, Estonia
>
> Skype me: callto:hkrosing
> Get Skype for free: http://www.skype.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!