pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Joins involving functions
2+ messages / 2 participants
[nested] [flat]

* Joins involving functions
@ 2006-04-07 21:01 Jeff Boes <jeff@endpoint.com>
  2006-04-11 11:42 ` Re: Joins involving functions Markus Schaber <schabi@logix-tt.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Jeff Boes @ 2006-04-07 21:01 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Joins involving functions
  2006-04-07 21:01 Joins involving functions Jeff Boes <jeff@endpoint.com>
@ 2006-04-11 11:42 ` Markus Schaber <schabi@logix-tt.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Markus Schaber @ 2006-04-11 11:42 UTC (permalink / raw)
  To: Jeff Boes <jeff@endpoint.com>; +Cc: pgsql-sql

Hi, Jeff Boes,

Jeff Boes wrote:

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

I don't exactly know what you want to achieve, so I'm just wildly guessing.

Do you really want the cross join between bar.* and f_foo(bar.b)?

If yes, use

SELECT x.b, y.b FROM bar x CROSS JOIN bar y;

If not, you maybe want

SELECT b, f_foo(b) FROM bar;

Or it may be you want something like:

SELECT * from bar CROSS JOIN (SELECT f_foo(bar.b) from bar) as foo ;


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-04-11 11:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-07 21:01 Joins involving functions Jeff Boes <jeff@endpoint.com>
2006-04-11 11:42 ` Markus Schaber <schabi@logix-tt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox