Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cast as compound type

Cast as compound type

From
David Fetter
Date:
Folks,

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.

Would others like to see such a feature?

If so, what pieces of the code would I be touching for the first
patch?

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

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

Re: Cast as compound type

From
"Pavel Stehule"
Date:
Hello

maybe I don't understand well your idea. There exist simple syntax -
table function

http://archives.postgresql.org/pgsql-patches/2007-02/msg00341.php

and it is standard

regards
Pavel Stehule

On 30/03/2008, David Fetter <david@fetter.org> wrote:
> Folks,
>
>  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.
>
>  Would others like to see such a feature?
>
>  If so, what pieces of the code would I be touching for the first
>  patch?
>
>  Cheers,
>  David.
>  --
>  David Fetter <david@fetter.org> http://fetter.org/
>  Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
>  Skype: davidfetter      XMPP: david.fetter@gmail.com
>
>  Remember to vote!
>  Consider donating to Postgres: http://www.postgresql.org/about/donate
>
>
>  --
>  Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>  To make changes to your subscription:
>  http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: Cast as compound type

From
David Fetter
Date:
On Sun, Mar 30, 2008 at 10:00:33PM +0200, Pavel Stehule wrote:
> Hello
> 
> maybe I don't understand well your idea. There exist simple syntax -
> table function
> 
> http://archives.postgresql.org/pgsql-patches/2007-02/msg00341.php
> 
> and it is standard

It's completely different from your patch, which specifies the return
type at the time you create the function.

This idea takes functions which return SETOF RECORD, that is functions
which determine their return type at run time instead of create time,
and short-cuts the cast that you need to do at run time.

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

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

Re: Cast as compound type

From
"korry"
Date:
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?

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


-- 
 Korry Douglas  <korryd@enterprisedb.com> EnterpriseDB    http://www.enterprisedb.com

Re: Cast as compound type

From
David Fetter
Date:
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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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