Message-ID: <4436D343.5010802@endpoint.com>
Date: Fri, 07 Apr 2006 17:01:55 -0400
From: Jeff Boes <jeff@endpoint.com>
Organization: End Point, Inc.
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Joins involving functions
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

I have a function that returns a row from a table, e.g.:

create table foo (a integer);

create function f_foo (integer) returns foo language 'sql'
as $foo$
  select * from foo where a = $1 limit 1
$foo$

create table bar (b integer);

select * from bar
cross join f_foo(bar.b) as foo;

Unfortunately, this last statement returns an error:

NOTICE:  adding missing FROM-clause entry for table "bar"
ERROR:  function expression in FROM may not refer to other relations of
same query level

Is there a method for joining function calls to tables? The only other
approach I can think of is to write another function that returns "setof".

-- 
Jeffery Boes  <><
jeff@endpoint.com