Re: Impossible with pl/pgsql?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Markus Bertheau ☭ <twanger(at)bluetwanger(dot)de>
Cc: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Impossible with pl/pgsql?
Date: 2005-05-30 16:21:34
Message-ID: 21974.1117470094@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Caad_Notes1 2005-05-30 19:03:13 Report to Sender
Previous Message Michael Fuhr 2005-05-30 16:13:33 Re: Impossible with pl/pgsql?