Обсуждение: function expression in FROM may not refer to other relations of same query level
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);
Philippe Lang wrote: > 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 This is as expected and required -- you cannot refer to other FROM clause relations. I believe SQL99 defines a clause (LATERAL IIRC) that would allow this, but it hasn't been implemented yet. > 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 Whats wrong with just using CASE: select id, usr, code, case when code = 1 then 'A' else 'Z' end as line1, case when code = 1 then 'A' else 'Z'end as line2 from tbl; id | usr | code | line1 | line2 ----+-------+------+-------+------- 1 | one | 1 | A | A 2 | two | 2 | Z | Z 3 | three | 1 | A | A (3 rows) Joe
Re: function expression in FROM may not refer to other relations of same query level
От
"Philippe Lang"
Дата:
Hello, > Whats wrong with just using CASE: > > select id, usr, code, > case when code = 1 then 'A' else 'Z' end as line1, > case when code = 1 then 'A' else 'Z' end as line2 from tbl; The code I showed in my last mail was actually test code only. The logic is more complicated, and I'm not sure it's possible to implement it with a SELECT... CASE. What's more, the "get_lines" function already exists, and is already used in different parts of the database. I would like, if possible, to use it without changing it, or duplicating code. Philippe
Re: function expression in FROM may not refer to other relations of same query level
От
"Philippe Lang"
Дата:
I found one solution:
select id, usr, code, (get_lines(code)).line1, (get_lines(code)).line2
from tbl;
The problem now is that get_lines is being called twice per line. (I check with a RAISE NOTICE).
Philippe
-----Message d'origine-----
De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] De la part de Philippe Lang
Envoyé : mercredi, 11. août 2004 08:41
À : pgsql-sql@postgresql.org
Objet : Re: [SQL] function expression in FROM may not refer to other relations of same query level
Hello,
> Whats wrong with just using CASE:
>
> select id, usr, code,
> case when code = 1 then 'A' else 'Z' end as line1,
> case when code = 1 then 'A' else 'Z' end as line2 from tbl;
The code I showed in my last mail was actually test code only. The logic is more complicated, and I'm not sure it's
possibleto implement it with a SELECT... CASE. What's more, the "get_lines" function already exists, and is already
usedin different parts of the database. I would like, if possible, to use it without changing it, or duplicating code.
Philippe
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: function expression in FROM may not refer to other relations of same query level
От
Rosser Schwarz
Дата:
Philippe Lang wrote: > The problem now is that get_lines is being called twice per line. Is get_lines() defined as IMMUTABLE? Should it be? /rls -- :wq
Re: function expression in FROM may not refer to other relations of same query level
От
"Philippe Lang"
Дата:
> > The problem now is that get_lines is being called twice per line. > > Is get_lines() defined as IMMUTABLE? Should it be? I have tried defining get_lines as "IMMUTABLE", or "WITH (iscachable)", but it keeps on getting called twice per line in the following query... select id, usr, code, (get_lines(code)).line1, (get_lines(code)).line2 from tbl; I wish there was a way to run the query like this: select id, usr, code, CAST(get_lines(code) as lines) from tbl;
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I wish there was a way to run the query like this:
> select
> id,
> usr,
> code,
> CAST(get_lines(code) as lines)
> from tbl;
You can do something like this:
regression=# create type complex as (r float8, i float8);
CREATE TYPE
regression=# create function fooey(float8) returns complex as
regression-# 'select $1 + 1, $1 + 2' language sql;
CREATE FUNCTION
regression=# select f1, (fooey).r, (fooey).i from
regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss; f1 | r |
i
-----------------------+-----------------------+----------------------- 0 | 1 |
2 -34.84 | -33.84 | -32.84 -1004.3 |
-1003.3 | -1002.3-1.2345678901234e+200 | -1.2345678901234e+200 |
-1.2345678901234e+200-1.2345678901234e-200| 1 | 2
(5 rows)
Note the odd-looking parenthesization --- you can't write just "fooey.r"
because that looks like it should be a table and field name, not a field
name that is selected from.
If the sub-select is too simple, as it is in this case, the planner is
likely to "flatten out" the query into
select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl;
thus defeating your purpose of not calling the function twice. The
currently best hack for preventing this is to add "OFFSET 0" to the
sub-select:
select f1, (fooey).r, (fooey).i from
(select f1, fooey(f1) as fooey from float8_tbl offset 0) ss;
regards, tom lane
On Thu, 12 Aug 2004, Philippe Lang wrote: > > > The problem now is that get_lines is being called twice per line. > > > > Is get_lines() defined as IMMUTABLE? Should it be? > > I have tried defining get_lines as "IMMUTABLE", or "WITH (iscachable)", > but it keeps on getting called twice per line in the following query... > > select > id, > usr, > code, > (get_lines(code)).line1, > (get_lines(code)).line2 > from tbl; > > I wish there was a way to run the query like this: > > select > id, > usr, > code, > CAST(get_lines(code) as lines) > from tbl; Note thatselect id, usr, code, get_lines(code) from tbl; should work in 8.0beta although you get the composite type as the last column, not two columns. sszabo=# select id, usr, code, get_lines(code) from tbl;id | usr | code | get_lines ----+-------+------+----------- 1 | one | 1 | (A,B) 2 | two | 2 | (Z,Z) 3 | three | 1 | (A,B) (3 rows) You can get individual columns with a bit of work and a subselect in from but you need to trick the system to not pull up the subselect to not get the function called twice per row.
Re: function expression in FROM may not refer to other relations of same query level
От
"Philippe Lang"
Дата:
Thanks a lot for your support. With a subselect and offset 0, the function is called only once per row, that's fine.
Here is the final test code, in case it can help anyone.
-----------------------------------------------
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;
RAISE NOTICE ''-------> get_lines was called...'';
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);
INSERT INTO tbl VALUES (4, 'four', 2);
select id, usr, code, (get_lines_data).line1, (get_lines_data).line2
from
( select id, usr, code, get_lines(code) as get_lines_data
from tbl offset 0
)
as ss;
-----------------------------------------------
Philippe Lang
-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : jeudi, 12. août 2004 16:31
À : Philippe Lang
Cc : pgsql-sql@postgresql.org
Objet : Re: [SQL] function expression in FROM may not refer to other relations of same query level
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> I wish there was a way to run the query like this:
> select
> id,
> usr,
> code,
> CAST(get_lines(code) as lines)
> from tbl;
You can do something like this:
regression=# create type complex as (r float8, i float8); CREATE TYPE regression=# create function fooey(float8)
returnscomplex as regression-# 'select $1 + 1, $1 + 2' language sql; CREATE FUNCTION regression=# select f1, (fooey).r,
(fooey).ifrom regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss; f1 | r
| i
-----------------------+-----------------------+----------------------- 0 | 1 |
2 -34.84 | -33.84 | -32.84 -1004.3 |
-1003.3 | -1002.3-1.2345678901234e+200 | -1.2345678901234e+200 |
-1.2345678901234e+200-1.2345678901234e-200| 1 | 2
(5 rows)
Note the odd-looking parenthesization --- you can't write just "fooey.r"
because that looks like it should be a table and field name, not a field name that is selected from.
If the sub-select is too simple, as it is in this case, the planner is likely to "flatten out" the query into
select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl;
thus defeating your purpose of not calling the function twice. The currently best hack for preventing this is to add
"OFFSET0" to the
sub-select:
select f1, (fooey).r, (fooey).i from
(select f1, fooey(f1) as fooey from float8_tbl offset 0) ss;
regards, tom lane