Re: function expression in FROM may not refer to other relations of same query level - Mailing list pgsql-sql

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