A sys func for a->b, b->c => a->c ?

From: Emi Lu <emilu(at)encs(dot)concordia(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: A sys func for a->b, b->c => a->c ?
Date: 2008-04-02 14:21:48
Message-ID: 47F3967C.9070104@encs.concordia.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Good Morning,

Someone has better solution about the following query situation?

table test with two columns with primary key (id1, id2)
id1, id2
=========
1 2
1 3
12 3
13 5

Query conditions:
=====================
(1) a->b => b->a
(2) a->b and a->c => a->c

Expected return:
id1 id2
===========
1 2
1 3
1 12

2 1
2 3
2 12

3 1
3 2
3 12

12 1
12 2
12 3

13 5

I did:

create view v_test AS
select id1 , id2 from test
union
select id2, id1 from test;

(
SELECT a.id1 , b.id2
FROM v_test AS a
left join v_test AS b
ON (a.id2 = b.id1)
WHERE a.id1 <> b.id2
)
UNION
(
SELECT id1, id2
FROM v_test
)
order by id1 ;

The query is a bit complex, do we have a better system func or query for
this?

Thanks a lot!

Browse pgsql-sql by date

  From Date Subject
Next Message chester c young 2008-04-02 17:42:36 apparent RI bug
Previous Message Volkan YAZICI 2008-04-02 05:48:03 Re: Sequential non unique IDs