Markus Bertheau ☭ <twanger@bluetwanger.de> writes:
> I have a function find() that returns a SETOF INT. I further have a
> function decorate as follows:
> CREATE TYPE decorate_type AS (id INT, name TEXT, author TEXT,
> last_change TIMESTAMP);
> CREATE FUNCTION decorate(INT)
> RETURNS decorate_type
> Now how would a query look like that involves find() and decorate() and
> returns
> id | name | author | last_change
> --------------------------------
> 4 | egg | john | 2003-05-05
> 5 | ham | dave | 2004-03-01
One way isselect decorate(find) from find();
which will give you something like
decorate
------------------------------------------(4,foo,bar,"2005-05-30 12:14:14.161292")(5,foo,bar,"2005-05-30
12:14:14.161292")
(2 rows)
If you want the columns of the rowtype broken apart, you can useselect (decorate(find)).* from find();
id | name | author | last_change
----+------+--------+---------------------------- 4 | foo | bar | 2005-05-30 12:14:17.571481 5 | foo | bar |
2005-05-3012:14:17.571481
(2 rows)
although I believe this will result in multiple evaluations of decorate()
per row. If decorate() is expensive you'll want to do something like
select (decorate).* from (select decorate(find) from find() offset 0) ss;
where the OFFSET clause serves as an optimization fence to prevent the
planner from folding this down to the same as the previous version.
(This is all assuming PG 8.0 or later)
regards, tom lane