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 7E66814B281C for ; Thu, 12 Aug 2004 11:30:40 -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 26665-03 for ; Thu, 12 Aug 2004 14:30:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0E0CB14B280D for ; Thu, 12 Aug 2004 11:30:38 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7CEUfp0028662; Thu, 12 Aug 2004 10:30:41 -0400 (EDT) To: "Philippe Lang" Cc: pgsql-sql@postgresql.org Subject: Re: function expression in FROM may not refer to other relations of same query level In-reply-to: <6C0CF58A187DA5479245E0830AF84F420803B7@poweredge.attiksystem.ch> References: <6C0CF58A187DA5479245E0830AF84F420803B7@poweredge.attiksystem.ch> Comments: In-reply-to "Philippe Lang" message dated "Thu, 12 Aug 2004 09:35:01 +0200" Date: Thu, 12 Aug 2004 10:30:41 -0400 Message-ID: <28661.1092321041@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/111 X-Sequence-Number: 18608 "Philippe Lang" writes: > I wish there was a way to run the query like this: > select > id, > usr, > code, > CAST(get_lines(code) as lines) > from tbl; You can do something like this: regression=# create type complex as (r float8, i float8); CREATE TYPE regression=# create function fooey(float8) returns complex as regression-# 'select $1 + 1, $1 + 2' language sql; CREATE FUNCTION regression=# select f1, (fooey).r, (fooey).i from regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss; f1 | r | i -----------------------+-----------------------+----------------------- 0 | 1 | 2 -34.84 | -33.84 | -32.84 -1004.3 | -1003.3 | -1002.3 -1.2345678901234e+200 | -1.2345678901234e+200 | -1.2345678901234e+200 -1.2345678901234e-200 | 1 | 2 (5 rows) Note the odd-looking parenthesization --- you can't write just "fooey.r" because that looks like it should be a table and field name, not a field name that is selected from. If the sub-select is too simple, as it is in this case, the planner is likely to "flatten out" the query into select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl; thus defeating your purpose of not calling the function twice. The currently best hack for preventing this is to add "OFFSET 0" to the sub-select: select f1, (fooey).r, (fooey).i from (select f1, fooey(f1) as fooey from float8_tbl offset 0) ss; regards, tom lane