Re: Using SETOF functions in SQL

On Tue, 2006-08-15 at 12:38 +0100, Pavel Velikhov wrote:
> SELECT t1.node, t2.node, path FROM t1,t2, connections(t1.id, t2.id) as
> path
>
> I get the notorious: ERROR: subquery in FROM may not refer to other
> relations of same query level
>

The FROM list must be a list of relations, but in that situation,
connections() creates a different relation for each relation in the join
of t1 and t2. You certainly don't want to join a variable number of
relations together (nor is that allowed).

You could make connections() return the entire set of all connections
and join based on t1.id and t2.id.

Another way to do it would be to do something like "SELECT t1.node,
t2.node, get_path(t1.node,t2.node) from t1, t2 where get_path
(t1.node,t2.node) is not null". Then just make get_path() return null if
the nodes aren't connected.

Regards,
Jeff Davis