Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Returning a RECORD, not SETOF RECORD

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Hallgren <thhal(at)mailblocks(dot)com>
Cc: Michael Fuhr <mike(at)fuhr(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [GENERAL] Returning a RECORD, not SETOF RECORD
Date: 2005-04-29 14:36:05
Message-ID: 5830.1114785365@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Thomas Hallgren <thhal(at)mailblocks(dot)com> writes:
> Ideally I'd like to write something like this:

> SELECT xyz(a, b) AS (x int, y int, z timestamptz) FROM abc;

> but that yields a syntax error.

While that's probably doable if anyone were really motivated,
I'm not sure it's worth the trouble in view of the recent OUT-parameter
improvements.  IMHO most of the use cases for such a thing would be
better served by declaring the function with OUT parameters.  The
AS-clause-column-list functionality was invented for functions where the
result type is truly not known when the function is written, such as
dblink.  But it's pretty hard to believe that many people need to write
such things.

Your example can be done like this in CVS tip:

regression=# create function xyz(int, int, out x int, out y int, out z timestamptz) as $$ select $1, $2, now() $$ language sql;
CREATE FUNCTION
regression=# select xyz(unique1,unique2) from tenk1 limit 5;
                   xyz
------------------------------------------
 (8800,0,"2005-04-29 10:26:37.738946-04")
 (1891,1,"2005-04-29 10:26:37.738946-04")
 (3420,2,"2005-04-29 10:26:37.738946-04")
 (9850,3,"2005-04-29 10:26:37.738946-04")
 (7164,4,"2005-04-29 10:26:37.738946-04")
(5 rows)

Notice that this returns the record as a single column.  In most cases
you would probably wish that the record were burst into multiple
columns, which you can do easily with

regression=# select (xyz(unique1,unique2)).* from tenk1 limit 5;
  x   | y |               z
------+---+-------------------------------
 8800 | 0 | 2005-04-29 10:27:53.197948-04
 1891 | 1 | 2005-04-29 10:27:53.197948-04
 3420 | 2 | 2005-04-29 10:27:53.197948-04
 9850 | 3 | 2005-04-29 10:27:53.197948-04
 7164 | 4 | 2005-04-29 10:27:53.197948-04
(5 rows)

but AFAICS that is not amenable to having an AS plastered on it (unless
the AS goes inside the parentheses, which'd be a really spectacular
abuse of the syntax).

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Matthew T. O'ConnorDate: 2005-04-29 15:40:13
Subject: Re: Feature freeze date for 8.1
Previous:From: Tom LaneDate: 2005-04-29 14:17:44
Subject: Re: [proposal] protocol extension to support loadable stream filters

pgsql-general by date

Next:From: Michael FuhrDate: 2005-04-29 15:08:57
Subject: Re: Problem: message type 0xxx arrived from server while idle
Previous:From: Tom LaneDate: 2005-04-29 14:04:17
Subject: Re: Increasing statistics results in worse estimates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group