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@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!