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

Поиск
Список
Период
Сортировка
От Philippe Lang
Тема function expression in FROM may not refer to other relations of same query level
Дата
Msg-id 6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch
обсуждение исходный текст
Ответы Re: function expression in FROM may not refer to other relations  (Joe Conway <mail@joeconway.com>)
Список 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);



В списке pgsql-sql по дате отправления:

Предыдущее
От: David Stanaway
Дата:
Сообщение: UPDATE FROM problem, multiple updates of same row don't seem to work
Следующее
От: Christoph Haller
Дата:
Сообщение: Re: Stored procedures and "pseudo" fields