combination of function to simple query makes query slow - Mailing list pgsql-sql

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
FROMmapping_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_nameFROM 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_idFROM 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.