X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 70E4A52806 for ; Mon, 30 May 2005 13:21:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70386-05 for ; Mon, 30 May 2005 16:21:44 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E030752805 for ; Mon, 30 May 2005 13:21:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4UGLYVa021975; Mon, 30 May 2005 12:21:34 -0400 (EDT) To: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= Cc: "'pgsql-sql@postgresql.org'" Subject: Re: Impossible with pl/pgsql? In-reply-to: <1117466155.20420.7.camel@dicaprio.akademie1.de> References: <1117466155.20420.7.camel@dicaprio.akademie1.de> Comments: In-reply-to Markus Bertheau =?UTF-8?Q?=E2=98=AD?= message dated "Mon, 30 May 2005 17:15:55 +0200" Date: Mon, 30 May 2005 12:21:34 -0400 Message-ID: <21974.1117470094@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/238 X-Sequence-Number: 21551 Markus Bertheau =?UTF-8?Q?=E2=98=AD?= 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