Re: [HACKERS] (SETOF) RECORD AS complex_type

2007-01-08 Thread Hannu Krosing 
Ü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


---(end of broadcast)---
TIP 6: explain analyze is your friend

[HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread 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
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread Tom Lane 
David Fetter [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread David Fetter 
On Wed, Dec 27, 2006 at 06:22:17PM -0500, Tom Lane wrote:
 David Fetter [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread Tom Lane 
David Fetter [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread David Fetter 
On Wed, Dec 27, 2006 at 06:36:56PM -0500, Tom Lane wrote:
 David Fetter [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread Andrew Dunstan 
Tom Lane wrote:
 David Fetter [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] (SETOF) RECORD AS complex_type

2006-12-27 Thread David Fetter 
On Wed, Dec 27, 2006 at 06:11:55PM -0600, Andrew Dunstan wrote:
 Tom Lane wrote:
  David Fetter [EMAIL PROTECTED] 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 [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq