Thread: 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);
--> 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

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

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

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

> > 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.

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