Thread: Returning a RECORD, not SETOF RECORD

I just discovered that my previous post concerning this had the same
subject line as a discussion that took place in January. I'm not asking
the same question though, so here I go again with my question about
syntax and feasibility.

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

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/