Hi,
I'm having some problems in combining a function within a standard
query.
I basically have two tables (see between hashes below):
* alias_table: objects can have different names (e.g. name1 is an alias
of name2, and name3 is an alias of name4)
* mapping_table: shows objects that 'lead to' other objects (e.g. the
object with name1 leads to the object with name5)
I want to traverse the mapping_table in a transparent way: if a first
object leads to a second object, and that second object leads to a third
object, I want to list the first and the third objects. So from the
example table below: name6 leads to name2, which is an alias of name1,
which in its turn leads to name5. The result of my query should be:
name6 leads to name5.
To accomplish this, I wrote a little function ('aliases_of'), as
described below between the hashes.
The query
SELECT aliases_of(1);
returns:
aliases_of
----------
1
2
, which is the expected output.
The query
SELECT * FROM mapping_table
WHERE object1_id IN ( SELECT aliases_of(2) );
returns:
object1_id | object1_name | object2_id | object2_name
-----------------------------------------------------
1 | name1 | 5 | name5
, which is the expected output, because it is equivalent to:
SELECT * FROM mapping_table
WHERE object1_id IN ( 1, 2 );
However, the query that would traverse the data:
SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
FROM mapping_table m1, mapping_table m2
WHERE m1.object2_id IN ( SELECT aliases_of(m2.object1_id) );
just keeps on running, and never seems to stop. (I must say that that
table in effect contains about a million records.)
The expected output would have been:
object1_id | object1_name | object2_id | object2_name
-----------------------------------------------------
6 | name6 | 5 | name5
I also tried to following, which doesn't work either.
SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
FROM mapping_table m1 JOIN mapping_table m2 ON ( m1.object2_id IN (
SELECT aliases_of(m2.object1_id) );
Is there a way to speed up this query? I'd also have to be able to
traverse the mapping_table ever further, following a path of things that
'lead to' other things.
I hope I made myself clear a bit...
Any help appreciated. Many thanks,
jan.
################################################################
(A) TABLE: alias_table
object1_id | object1_name | object2_id | object2_name
-----------------------------------------------------
1 | name1 | 2 | name2
3 | name3 | 4 | name4
(B) TABLE: mapping_table
object1_id | object1_name | object2_id | object2_name
-----------------------------------------------------
1 | name1 | 5 | name5
6 | name6 | 2 | name2
(C) FUNCTION: aliases_of
CREATE FUNCTION aliases_of(bigint) RETURNS SETOF bigint
AS 'SELECT $1
UNION
SELECT object1_id FROM alias_table WHERE object2_id = $1
UNION
SELECT object2_id FROM alias_table WHERE object1_id = $1
'
LANGUAGE SQL;
################################################################
---------The obligatory disclaimer--------
The information contained in this e-mail (including any attachments) is
confidential and is intended for the use of the addressee only. The
opinions expressed within this e-mail (including any attachments) are
the opinions of the sender and do not necessarily constitute those of
Roslin Institute (Edinburgh) ("the Institute") unless specifically
stated by a sender who is duly authorised to do so on behalf of the
Institute.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
