Message-ID: <411908DA.6090305@joeconway.com>
Date: Tue, 10 Aug 2004 10:41:46 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803
MIME-Version: 1.0
To: Philippe Lang <philippe.lang@attiksystem.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: function expression in FROM may not refer to other relations
References: <6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch>
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F420803B2@poweredge.attiksystem.ch>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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