Re: Impossible with pl/pgsql?

Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <twanger(at)bluetwanger(dot)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 is
select 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 use
select (decorate(find)).* from find();

id | name | author | last_change
----+------+--------+----------------------------
4 | foo | bar | 2005-05-30 12:14:17.571481
5 | foo | bar | 2005-05-30 12: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