Thread: Using SETOF functions in SQL

Using SETOF functions in SQL

From

Pavel Velikhov

Date:

15 August 2006, 15:40:42

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

Re: Using SETOF functions in SQL

From

Jeff Davis

Date:

15 August 2006, 16:06:33

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