Skip site navigation (1) Skip section navigation (2)

Re: Cast as compound type

From: David Fetter <david(at)fetter(dot)org>
To: korry <korry(dot)douglas(at)enterprisedb(dot)com>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Cast as compound type
Date: 2008-04-01 16:35:03
Message-ID: 20080401163502.GA25159@fetter.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Mar 31, 2008 at 07:18:43PM -0400, Korry Douglas wrote:
> David Fetter wrote:
>> I'd like to take a whack at making set-returning functions
>> returning SETOF RECORD a little more fun to use.  Let's imagine
>> that we have a table foo and a function returning SETOF RECORD that
>> can return foos.  The call might look something like:
>>
>> SELECT a, b, c
>> FROM f(ROW OF foo)
>> WHERE ...;
>>
>> This would make it much easier and less error-prone to use SETOF
>> RECORD.
>>   
> David, it sounds like you really want to declare the return type of
> the function?  In your above example, you want to say that, in this
> particular invocation, function f() returns a SETOF foo's.  Is that
> correct?

Yes.

> If you were to create function that returns a RECORD (not a SETOF RECORD), 
> you would call it like this:
>
>    SELECT * FROM f() AS (column1 type1, column2 type2, column3 type3);
>
> In your case, I think you want to declare the return type using an 
> explicitly defined composite type (possibly a table row); which would imply 
> syntax such as:
>
>    SELECT * FROM f() AS (foo);
>       or
>    SELECT * FROM f() AS (foo.*);
>
> So, it seems like you want the syntax to look more like:
>
>    SELECT a,b,c, FROM f() AS (SETOF foo);
>
> Does that make sense to you?  Your original syntax implied that the
> "ROW OF foo" was somehow related to the function arguments.
>                                    -- Korry

I see.

Thinking a little further, it seems we could do this a little more
generally.  Here's what it could look like.

AS (<column_set_description> {, <column_set_description})

<column_set_description> =
    <column_name> <simple_data_type_name> |
    [ <compound_data_type_prefix> ] <compound_data_type_name>;

<compound_data_type_prefix> would be prepended to each column in the
output, so for a compound type foo(i int, t text, p point), AS (f foo)
would produce output columns f.i, f.t and f.p.  Typical uses for this
would be to keep a set of column names distinct.

Cheers,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-04-01 16:42:21
Subject: Re: Improve shutdown during online backup
Previous:From: Simon RiggsDate: 2008-04-01 16:17:34
Subject: Re: [HACKERS] Minor changes to Recovery related code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group