MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: function expression in FROM may not refer to other relations of
	same query level
Date: Fri, 13 Aug 2004 10:52:11 +0200
content-class: urn:content-classes:message
Message-ID: <6C0CF58A187DA5479245E0830AF84F420B0324@poweredge.attiksystem.ch>
Thread-Topic: [SQL] function expression in FROM may not refer to other
	relations of same query level
Thread-Index: AcSAePMdPf8PdT2dRyuQpQvVPeggtgAmV7xQ
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

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" <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=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