Postgres Pro
Downloads
Home   >   mailing lists

Thread: (SETOF) RECORD AS complex_type

(SETOF) RECORD AS complex_type

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

Remember to vote!

Re: (SETOF) RECORD AS complex_type

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> 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;

The problem with that is that "AS foo" already has a meaning, and it's
not this one.
        regards, tom lane

Re: (SETOF) RECORD AS complex_type

From
David Fetter
Date:
On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > 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;
> 
> The problem with that is that "AS foo" already has a meaning, and it's
> not this one.

How about "AS (foo)" ?

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!

Re: (SETOF) RECORD AS complex_type

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
>> The problem with that is that "AS foo" already has a meaning, and it's
>> not this one.

> How about "AS (foo)" ?

What if you want to specify an alias?  This doesn't work:
FROM myverylongfunctionname(...) AS alias(typename)

because, again, that syntax already has a meaning.

You could possibly do something with a cast:
FROM CAST(myfunc(...) AS typename) [ AS alias ]

This is at least syntactically OK.  Not sure what the implementation
issues might be.
        regards, tom lane

Re: (SETOF) RECORD AS complex_type

From
David Fetter
Date:
On Wed, Dec 27, 2006 at 06:36:56PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
> >> The problem with that is that "AS foo" already has a meaning, and it's
> >> not this one.
> 
> > How about "AS (foo)" ?
> 
> What if you want to specify an alias?  This doesn't work:
> 
>     FROM myverylongfunctionname(...) AS alias(typename)
> 
> because, again, that syntax already has a meaning.

Oops.

> You could possibly do something with a cast:
> 
>     FROM CAST(myfunc(...) AS typename) [ AS alias ]

Works for me.

> This is at least syntactically OK.  Not sure what the implementation
> issues might be.

I'm not finding any CASTs from RECORD right offhand.  Where might
those be?

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!

Re: (SETOF) RECORD AS complex_type

From
"Andrew Dunstan"
Date:
Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
>> On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
>>> The problem with that is that "AS foo" already has a meaning, and it's
>>> not this one.
>
>> How about "AS (foo)" ?
>
> What if you want to specify an alias?  This doesn't work:
>
>     FROM myverylongfunctionname(...) AS alias(typename)
>
> because, again, that syntax already has a meaning.
>
> You could possibly do something with a cast:
>
>     FROM CAST(myfunc(...) AS typename) [ AS alias ]
>
> This is at least syntactically OK.  Not sure what the implementation
> issues might be.
>


For some time now I have wanted to genaralise the use of LIKE in type
expressions, which might perhaps fit David's need. Something like
 SELECT * from foo() AS bar (LIKE blurfl);

The nice thing about this is that you could augment the type expression:
 SELECT * from foo() AS bar (extra_info text, LIKE blurfl);

cheers

andrew

Re: (SETOF) RECORD AS complex_type

From
David Fetter
Date:
On Wed, Dec 27, 2006 at 06:11:55PM -0600, Andrew Dunstan wrote:
> Tom Lane wrote:
> > David Fetter <david@fetter.org> writes:
> >> On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
> >>> The problem with that is that "AS foo" already has a meaning, and it's
> >>> not this one.
> >
> >> How about "AS (foo)" ?
> >
> > What if you want to specify an alias?  This doesn't work:
> >
> >     FROM myverylongfunctionname(...) AS alias(typename)
> >
> > because, again, that syntax already has a meaning.
> >
> > You could possibly do something with a cast:
> >
> >     FROM CAST(myfunc(...) AS typename) [ AS alias ]
> >
> > This is at least syntactically OK.  Not sure what the implementation
> > issues might be.
> >
> 
> 
> For some time now I have wanted to genaralise the use of LIKE in type
> expressions, which might perhaps fit David's need. Something like
> 
>   SELECT * from foo() AS bar (LIKE blurfl);
> 
> The nice thing about this is that you could augment the type expression:
> 
>   SELECT * from foo() AS bar (extra_info text, LIKE blurfl);

That would be really handy.  As with CREATE TABLE, you could pile
together several LIKEs and get whatever you needed :)

Cheers,
D

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

Remember to vote!

Re: (SETOF) RECORD AS complex_type

From
Hannu Krosing
Date:
Ü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);
hannu$# $$;
CREATE FUNCTION
hannu=# select * from bar(1);a | b |   c   |  d
---+---+-------+------1 | 1 | (1,1) | text1 | 1 | (1,1) | text1 | 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

Re: (SETOF) RECORD AS complex_type

From
David Fetter
Date:
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!