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

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);=20
--> 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)
);=20
=20
CREATE TABLE public.tbl
(
id int4 PRIMARY KEY,
usr varchar(10),=20
code int4
) WITHOUT OIDS;
=20
CREATE FUNCTION public.get_lines(int4)
RETURNS lines AS
'
DECLARE

code ALIAS FOR $1;=20

lines lines%rowtype;

BEGIN
=20
IF code =3D 1 THEN
lines.line1 =3D ''A'';=20
lines.line2 =3D ''B'';=20
ELSE=20
lines.line1 =3D ''Z'';=20
lines.line2 =3D ''Z'';=20
END IF;=20

RETURN lines;

END;
'
LANGUAGE 'plpgsql' VOLATILE;=20
=20
INSERT INTO tbl VALUES (1, 'one', 1);=20
INSERT INTO tbl VALUES (2, 'two', 2);=20
INSERT INTO tbl VALUES (3, 'three', 1);=20


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: function expression in FROM may not refer to other relations

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

Hello,

> Whats wrong with just using CASE:
>=20
> select id, usr, code,
> case when code =3D 1 then 'A' else 'Z' end as line1,
> case when code =3D 1 then 'A' else 'Z' end as line2 from tbl;=20

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

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

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=E9 : mercredi, 11. ao=FBt 2004 08:41
=C0 : pgsql-sql@postgresql.org
Objet : Re: [SQL] function expression in FROM may not refer to other relati=
ons of same query level

Hello,

> Whats wrong with just using CASE:
>=20
> select id, usr, code,
> case when code =3D 1 then 'A' else 'Z' end as line1,
> case when code =3D 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 SE=
LECT... 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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

> > The problem now is that get_lines is being called twice per line.
>=20
> 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;



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

"Philippe Lang" 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: function expression in FROM may not refer to other relations

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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

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 =3D 1 THEN
lines.line1 =3D ''A'';
lines.line2 =3D ''B'';
ELSE
lines.line1 =3D ''Z'';
lines.line2 =3D ''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]=20
Envoy=E9 : jeudi, 12. ao=FBt 2004 16:31
=C0 : Philippe Lang
Cc : pgsql-sql@postgresql.org
Objet : Re: [SQL] function expression in FROM may not refer to other relati=
ons of same query level=20

"Philippe Lang" 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=3D# create type complex as (r float8, i float8); CREATE TYPE reg=
ression=3D# create function fooey(float8) returns complex as regression-# '=
select $1 + 1, $1 + 2' language sql; CREATE FUNCTION regression=3D# select =
f1, (fooey).r, (fooey).i from regression-# (select f1, fooey(f1) as fooey f=
rom 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 na=
me that is selected from.

If the sub-select is too simple, as it is in this case, the planner is like=
ly 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 current=
ly 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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend