Обсуждение: Using SETOF functions in SQL
Hi!
Is it possible to use a function that returns SETOF and doesn't take constant parameters?
I have a function 'connections(id1 bigint, id2 bigint) returns setof text' that I use to list all paths from id1 to id2. However, I haven't figured out a way to call the function with non-constant functions. For example, if I try to write:
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
Is there a way to reformulate the query in SQL, or I am stuck with PgSQL now?
Thanks!
Pavel Velikhov
ISP RAS
Is it possible to use a function that returns SETOF and doesn't take constant parameters?
I have a function 'connections(id1 bigint, id2 bigint) returns setof text' that I use to list all paths from id1 to id2. However, I haven't figured out a way to call the function with non-constant functions. For example, if I try to write:
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
Is there a way to reformulate the query in SQL, or I am stuck with PgSQL now?
Thanks!
Pavel Velikhov
ISP RAS
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