function expression in FROM may not refer to other relations of same query level

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: function expression in FROM may not refer to other relations of same query level
Date: 2004-08-10 13:09:50
Message-ID: 6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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);

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christoph Haller 2004-08-10 13:44:44 Re: Stored procedures and "pseudo" fields
Previous Message David Stanaway 2004-08-09 20:16:29 UPDATE FROM problem, multiple updates of same row don't seem to work