pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction expression in FROM may not refer to other relations of same query level
9+ messages / 5 participants
[nested] [flat]
* function expression in FROM may not refer to other relations of same query level
@ 2004-08-10 13:09 Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-10 17:41 ` Re: function expression in FROM may not refer to other relations Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 9+ messages in thread
From: Philippe Lang @ 2004-08-10 13:09 UTC (permalink / raw)
To: 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);
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations
2004-08-10 13:09 function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
@ 2004-08-10 17:41 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 9+ messages in thread
From: Joe Conway @ 2004-08-10 17:41 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
@ 2004-08-11 06:41 Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 0 replies; 9+ messages in thread
From: Philippe Lang @ 2004-08-11 06:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
@ 2004-08-11 08:24 Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-11 14:41 ` Re: function expression in FROM may not refer to other relations of same query level Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Philippe Lang @ 2004-08-11 08:24 UTC (permalink / raw)
To: pgsql-sql
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 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
2004-08-11 08:24 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
@ 2004-08-11 14:41 ` Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rosser Schwarz @ 2004-08-11 14:41 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
@ 2004-08-12 07:35 Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-12 14:30 ` Re: function expression in FROM may not refer to other relations of same query level Tom Lane <tgl@sss.pgh.pa.us>
2004-08-12 14:36 ` Re: function expression in FROM may not refer to other relations Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 9+ messages in thread
From: Philippe Lang @ 2004-08-12 07:35 UTC (permalink / raw)
To: pgsql-sql
> > 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;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
2004-08-12 07:35 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
@ 2004-08-12 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-08-12 14:30 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations
2004-08-12 07:35 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
@ 2004-08-12 14:36 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2004-08-12 14:36 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
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 that
select 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: function expression in FROM may not refer to other relations of same query level
@ 2004-08-13 08:52 Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 0 replies; 9+ messages in thread
From: Philippe Lang @ 2004-08-13 08:52 UTC (permalink / raw)
To: pgsql-sql
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) 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-08-13 08:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-10 13:09 function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-10 17:41 ` Re: function expression in FROM may not refer to other relations Joe Conway <mail@joeconway.com>
2004-08-11 06:41 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-11 08:24 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-11 14:41 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2004-08-12 07:35 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
2004-08-12 14:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-12 14:36 ` Re: function expression in FROM may not refer to other relations Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-13 08:52 Re: function expression in FROM may not refer to other relations of same query level Philippe Lang <philippe.lang@attiksystem.ch>
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