From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: function expression in FROM may not refer to other relations of same query level |
Date: | 2004-08-12 14:30:41 |
Message-ID: | 28661.1092321041@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-08-12 14:36:37 | Re: function expression in FROM may not refer to other relations |
Previous Message | Devin Whalen | 2004-08-12 14:20:17 | Sending errors from psql to error file |