Re: 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/