From: Philippe Lang <philippe.lang@attiksystem.ch>
To: pgsql-sql@postgresql.org
Subject: function expression in FROM may not refer to other relations of same query level
Date: Tue, 10 Aug 2004 15:09:50 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch> (raw)
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);
--> 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)
);
CREATE TABLE public.tbl
(
id int4 PRIMARY KEY,
usr varchar(10),
code int4
) WITHOUT OIDS;
CREATE FUNCTION public.get_lines(int4)
RETURNS lines AS
'
DECLARE
code ALIAS FOR $1;
lines lines%rowtype;
BEGIN
IF code = 1 THEN
lines.line1 = ''A'';
lines.line2 = ''B'';
ELSE
lines.line1 = ''Z'';
lines.line2 = ''Z'';
END IF;
RETURN lines;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
INSERT INTO tbl VALUES (1, 'one', 1);
INSERT INTO tbl VALUES (2, 'two', 2);
INSERT INTO tbl VALUES (3, 'three', 1);
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: philippe.lang@attiksystem.ch
Subject: Re: function expression in FROM may not refer to other relations of same query level
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch>
* 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