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

[Philippe Lang]

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]