Getting FK relationships from information_schema - Mailing list pgsql-sql

I'm trying to get my application to deduce foreign key relationships 
automatically so it can perform appropriate joins for the user.  I'm new 
to information_schema and having problems getting what I want.  Here is 
a test script to be run on a database called "test."
------------------------------------------------------------------------------------
-- Demonstrate issues with information_schema

create table empl (   empl_pk     int4    primary key,   name        varchar
);

create table empl_addr (   empl_k      int4    references empl,   type        varchar,   address     varchar,   primary
key(empl_k, type)
 
);

create table doc (   doc_pk      int4    primary key,   name        varchar
);

create table empl_doc (   empl_k      int4,   doc_k       int4,
   primary key (empl_k,doc_k),   foreign key (empl_k) references empl,   foreign key (doc_k) references doc
);

create table doc_empl (   doc_k       int4,   empl_k      int4,
   primary key (doc_k,empl_k),   foreign key (doc_k) references doc,   foreign key (empl_k) references empl
);

-- Show info about the tables' primary keys
select   tc.table_name,   tc.constraint_type,   cu.column_name,   cu.ordinal_position
   from   information_schema.key_column_usage cu,   information_schema.table_constraints tc
   where       cu.constraint_name = tc.constraint_name   and         cu.table_name = tc.table_name
   and         tc.constraint_type = 'PRIMARY KEY'
   and         tc.table_catalog = 'test'   and         tc.table_schema = 'public'   order by 2
;

-- Show info about the tables' foreign keys
select   tc.table_name,   tc.constraint_type,   cu.column_name,   cu.ordinal_position
   from   information_schema.key_column_usage cu,   information_schema.table_constraints tc
   where       cu.constraint_name = tc.constraint_name   and         cu.table_name = tc.table_name
   and         tc.constraint_type = 'FOREIGN KEY'
   and         tc.table_catalog = 'test'   and         tc.table_schema = 'public'   order by 2
;

-- Show constraints for our table
select   constraint_name,   table_name,   constraint_type, is_deferrable, initially_deferred
   from information_schema.table_constraints
   order by 2,3,1
;

-- Show foreign key constraints
select
--    *   constraint_name,   unique_constraint_name,   match_option,update_rule,delete_rule
   from information_schema.referential_constraints
   order by 2,1
;
------------------------------------------------------------------------------------
This produces the following output:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLEtable_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------empl       | PRIMARY KEY     | empl_pk     |
  1empl_addr  | PRIMARY KEY     | empl_k      |                1empl_addr  | PRIMARY KEY     | type        |
   2doc        | PRIMARY KEY     | doc_pk      |                1empl_doc   | PRIMARY KEY     | empl_k      |
    1empl_doc   | PRIMARY KEY     | doc_k       |                2doc_empl   | PRIMARY KEY     | doc_k       |
     1doc_empl   | PRIMARY KEY     | empl_k      |                2
 
(8 rows)
table_name | constraint_type | column_name | ordinal_position
------------+-----------------+-------------+------------------empl_addr  | FOREIGN KEY     | empl_k      |
  1empl_doc   | FOREIGN KEY     | empl_k      |                1empl_doc   | FOREIGN KEY     | doc_k       |
   1doc_empl   | FOREIGN KEY     | doc_k       |                1doc_empl   | FOREIGN KEY     | empl_k      |
    1
 
(5 rows)
constraint_name | table_name | constraint_type | is_deferrable | 
initially_deferred
-----------------+------------+-----------------+---------------+--------------------doc_pkey        | doc        |
PRIMARYKEY     | NO            | NO$1              | doc_empl   | FOREIGN KEY     | NO            | NO$2              |
doc_empl  | FOREIGN KEY     | NO            | NOdoc_empl_pkey   | doc_empl   | PRIMARY KEY     | NO            |
NOempl_pkey      | empl       | PRIMARY KEY     | NO            | NO$1              | empl_addr  | FOREIGN KEY     | NO
          | NOempl_addr_pkey  | empl_addr  | PRIMARY KEY     | NO            | NO$1              | empl_doc   | FOREIGN
KEY    | NO            | NO$2              | empl_doc   | FOREIGN KEY     | NO            | NOempl_doc_pkey   |
empl_doc  | PRIMARY KEY     | NO            | NO
 
(10 rows)
constraint_name | unique_constraint_name | match_option | update_rule | 
delete_rule
-----------------+------------------------+--------------+-------------+-------------$1              | doc_pkey
     | NONE         | NO ACTION   | 
 
NO ACTION$2              | doc_pkey               | NONE         | NO ACTION   | 
NO ACTION$1              | empl_pkey              | NONE         | NO ACTION   | 
NO ACTION$1              | empl_pkey              | NONE         | NO ACTION   | 
NO ACTION$2              | empl_pkey              | NONE         | NO ACTION   | 
NO ACTION
(5 rows)

I can determine all the primary key fields nicely, and I can tell what 
fields are foreign keys.  The problem is, I can't determine where the 
foreign keys are pointing.  The problem is, the constraint names ($1, 
$2, etc.) are not unique so I don't know how to join the third query 
into the fourth.

For example, if I want to know about the foreign keys for empl_doc, I 
can determine from query 3 that there are two foreign key constraints on 
that table and they are called $1 and $2.  From query 4, I should be 
able to see what primary key constraints they point to, except there are 
3 $1's and 2 $2's in that table so I can't really see where they point.  
(Notice that the third and fourth records in the last query are identical!)

Should I be looking somehow else in information_schema to see where 
foreign keys link?  Or am I correct that either:

1. The automatically generated FK constraint_name should be guaranteed 
to be unique; or
2. There should be an additional column in the last query somewhere to 
tell me what table the FK reference is coming from.

Kyle