Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group