(SETOF) RECORD AS complex_type

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

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!

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

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!

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

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!

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

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!