Re: Cast as compound type

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