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 2C30414B27FF for ; Tue, 10 Aug 2004 10:10:02 -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 71894-08 for ; Tue, 10 Aug 2004 13:09:52 +0000 (GMT) Received: from poweredge.attiksystem.ch (adsl-soholight-4-c59-p029.vtx.ch [212.147.59.29]) by svr1.postgresql.org (Postfix) with ESMTP id 1223514B27FC for ; Tue, 10 Aug 2004 10:09:50 -0300 (ADT) MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: function expression in FROM may not refer to other relations of same query level X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 Date: Tue, 10 Aug 2004 15:09:50 +0200 content-class: urn:content-classes:message Message-ID: <6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: function expression in FROM may not refer to other relations of same query level Thread-Index: AcR+21F62pkSnvoWRx6+ppLsgmD38g== From: "Philippe Lang" To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200408/70 X-Sequence-Number: 18567 Hello, I'm trying to use the ROWTYPE return value of a plpgsql function in a SELECT query. The test code is below. The following query is accepted: select id, usr, code, line1, line2 from tbl, get_lines(1); id usr code line1 line2 ---------------------------------- 1 one 1 A B 2 two 2 A B 3 three 1 A B But the same query with a parameter returns an error: select id, usr, code, line1, line2 from tbl, get_lines(code);=20 --> ERROR: function expression in FROM may not refer to other relations of same query level Is there another way to run this query and get: id usr code line1 line2 ---------------------------------- 1 one 1 A B 2 two 2 Z Z 3 three 1 A B Thanks ---------------------------------------- TEST CODE ---------------------------------------- CREATE TYPE public.lines AS ( line1 varchar(10), line2 varchar(10) );=20 =20 CREATE TABLE public.tbl ( id int4 PRIMARY KEY, usr varchar(10),=20 code int4 ) WITHOUT OIDS; =20 CREATE FUNCTION public.get_lines(int4) RETURNS lines AS ' DECLARE code ALIAS FOR $1;=20 lines lines%rowtype; BEGIN =20 IF code =3D 1 THEN lines.line1 =3D ''A'';=20 lines.line2 =3D ''B'';=20 ELSE=20 lines.line1 =3D ''Z'';=20 lines.line2 =3D ''Z'';=20 END IF;=20 RETURN lines; END; ' LANGUAGE 'plpgsql' VOLATILE;=20 =20 INSERT INTO tbl VALUES (1, 'one', 1);=20 INSERT INTO tbl VALUES (2, 'two', 2);=20 INSERT INTO tbl VALUES (3, 'three', 1);=20