Cast as compound type

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 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

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

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