Returning a RECORD, not SETOF RECORD

On Fri, Apr 22, 2005 at 12:24:26AM +0200, Thomas Hallgren wrote:

CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...'
CREATE TABLE abc(a int, b int);

Now I want to call my xyz function once for each row in abc and I want
my RECORD to be (x int, y int, z timestamptz). How do I write that
query? I.e. where do specify my RECORD definition? Is it possible at
all? 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.

What version of PostgreSQL are you using, and could the function
return a specific composite type instead of RECORD? The following
works in 8.0.2:

CREATE TYPE xyztype AS (
x integer,
y integer,
z timestamp with time zone
);

CREATE FUNCTION xyz(arg1 integer, arg2 integer) RETURNS xyztype AS $$
DECLARE
rec xyztype;
BEGIN
rec.x := arg1 + 5;
rec.y := arg2 + 5;
rec.z := timeofday();
RETURN rec;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE TABLE abc (
a integer,
b integer
);

INSERT INTO abc (a, b) VALUES (10, 20);
INSERT INTO abc (a, b) VALUES (30, 40);

SELECT *, (xyz(a, b)).* FROM abc;
a | b | x | y | z
----+----+----+----+-------------------------------
10 | 20 | 15 | 25 | 2005-04-28 12:47:03.762354-06
30 | 40 | 35 | 45 | 2005-04-28 12:47:03.762812-06
(2 rows)

SELECT z, y, x, b, a FROM (SELECT *, (xyz(a, b)).* FROM abc) AS s;
z | y | x | b | a
-------------------------------+----+----+----+----
2005-04-28 12:47:17.953952-06 | 25 | 15 | 20 | 10
2005-04-28 12:47:17.954543-06 | 45 | 35 | 40 | 30
(2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael,
Thanks for your reply on this.

CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...'
CREATE TABLE abc(a int, b int);

Now I want to call my xyz function once for each row in abc and I want
my RECORD to be (x int, y int, z timestamptz). How do I write that
query? I.e. where do specify my RECORD definition? Is it possible at
all? 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.

What version of PostgreSQL are you using

The latest and greatest from CVS.

, and could the function
return a specific composite type instead of RECORD?

This is not for a specific use-case. I want to provide rich
functionality in PL/Java but I don't understand how the actual RECORD
type is determined in cases where you don't use the function in a FROM
clause where it only makes sense (to me at least) to use a function
returning SETOF RECORD.

Wouldn't it make sense to be able to define a record in the projection
part of a query, similar to what I was attempting with my SELECT? Has
this been discussed or is it just considered as not very useful?

Regards,
Thomas Hallgren

On Thu, Apr 28, 2005 at 09:47:45PM +0200, Thomas Hallgren wrote:

What version of PostgreSQL are you using

The latest and greatest from CVS.

Which branch? HEAD? REL8_0_STABLE?

Wouldn't it make sense to be able to define a record in the projection
part of a query, similar to what I was attempting with my SELECT? Has
this been discussed or is it just considered as not very useful?

Sounds reasonable to me, but if it's currently possible then I
haven't yet figured out how to do it. I can't remember if it's
been discussed before or not. If nobody answers here then you
might try pgsql-hackers.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr wrote:

On Thu, Apr 28, 2005 at 09:47:45PM +0200, Thomas Hallgren wrote:

What version of PostgreSQL are you using

The latest and greatest from CVS.

Which branch? HEAD? REL8_0_STABLE?

Sorry. To me "latest" always defaults to HEAD and by "greatest" I mean
the coming 8.1.

Wouldn't it make sense to be able to define a record in the projection
part of a query, similar to what I was attempting with my SELECT? Has
this been discussed or is it just considered as not very useful?

Sounds reasonable to me, but if it's currently possible then I
haven't yet figured out how to do it. I can't remember if it's
been discussed before or not. If nobody answers here then you
might try pgsql-hackers.

Ok. Thanks. I'll redirect this to hackers and see if I have any luck
there. My original question was:

I do the following:

CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...'
CREATE TABLE abc(a int, b int);

Now I want to call my xyz function once for each row in abc and I want
my RECORD to be (x int, y int, z timestamptz). How do I write that
query? I.e. where do specify my RECORD definition? Is it possible at
all? 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.

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.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

On Fri, Apr 29, 2005 at 10:36:05AM -0400, Tom Lane wrote:

regression=# select (xyz(unique1,unique2)).* from tenk1 limit 5;

This is a little off topic, but I've noticed that the above invokes
the function once per output column:

CREATE FUNCTION xyz(INOUT x integer, INOUT y integer, OUT z integer) AS $$
BEGIN
RAISE INFO 'calling xyz';
z := x + y;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT xyz(1,2);
INFO: calling xyz
xyz
---------
(1,2,3)
(1 row)

SELECT (xyz(1,2)).*;
INFO: calling xyz
INFO: calling xyz
INFO: calling xyz
x | y | z
---+---+---
1 | 2 | 3
(1 row)

Is that because the splat causes the query to be expanded into
"SELECT (xyz(1,2)).x, (xyz(1,2)).y, (xyz(1,2)).z"? Is it possible
or desirable to optimize that into a single call, at least if the
function were stable or immutable?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:

On Fri, Apr 29, 2005 at 10:36:05AM -0400, Tom Lane wrote:

regression=# select (xyz(unique1,unique2)).* from tenk1 limit 5;

This is a little off topic, but I've noticed that the above invokes
the function once per output column:

Yeah, that is unfortunate but doesn't seem very easy to fix.

regards, tom lane