From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Bertheau ☭ <twanger@bluetwanger.de>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Impossible with pl/pgsql?
Date: Mon, 30 May 2005 12:21:34 -0400
Message-ID: <21974.1117470094@sss.pgh.pa.us> (raw)
In-Reply-To: <1117466155.20420.7.camel@dicaprio.akademie1.de>
References: <1117466155.20420.7.camel@dicaprio.akademie1.de>
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, twanger@bluetwanger.de
Subject: Re: Impossible with pl/pgsql?
In-Reply-To: <21974.1117470094@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox