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

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

From
"Philippe Lang"
Date:
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);



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

From
Joe Conway
Date:
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


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

From
Stephan Szabo
Date:
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