From: Philippe Lang <philippe.lang@attiksystem.ch>
To: pgsql-sql@postgresql.org
Subject: Re: function expression in FROM may not refer to other relations of same query level
Date: Fri, 13 Aug 2004 10:52:11 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F420B0324@poweredge.attiksystem.ch> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philippe.lang@attiksystem.ch
Subject: Re: function expression in FROM may not refer to other relations of same query level
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F420B0324@poweredge.attiksystem.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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