pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ian Morgan <imorgan@webcon.net>
To: pgsql-sql@postgresql.org
Subject: How to discover foreign keys (without pulling hair out)
Date: Tue, 23 Apr 2002 02:54:31 -0400 (EDT)
Message-ID: <Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net> (raw)

Given a particular table, I want to get a list of all the foreign keys and
what they refer to, something like:

 local table | local field | foreign table | foreign field
-------------+-------------+---------------+---------------
 companies   | prime       | contacts      | contact_id
 companies   | referer     | contacts      | contact_id
 contacts    | company_id  | companies     | company_id
etc..

Here's as far as I've gotten:

To find the foreign keys in the "companies" table, do:

SELECT  tgargs
FROM    pg_trigger
WHERE   tgrelid = (select oid from pg_class where relname='companies')
        AND
        tgfoid = (select oid from pg_proc where proname='RI_FKey_check_ins')
;

                                      tgargs
-----------------------------------------------------------------------------
co.pri-c\000companies\000contacts\000UNSPECIFIED\000prime\000contact_id\000
co.ref-c\000companies\000contacts\000UNSPECIFIED\000referer\000contact_id\000
(2 rows)

These are the 6 args to the RI_FKey_check_ins function that is called in
relation to foreign key checking.

1 = trigger name
2 = local table
3 = foreign table
4 = ? (what is this one?)
5 = local field
6 = foreign field

With painstaking use of position(), octet_length(), and substring(), one can
extract the appropriate fields. Here's just one:

select substring(
  (
    select substring(
      tgargs,
      ( position('\\000'::bytea in tgargs)
        + octet_length('\\000companies\\000'::bytea)
      )
    ) from test limit 1
  )
  from 1 for (
    select position('\\000'::bytea in
      (
        select substring(
          tgargs,
          ( position('\\000'::bytea in tgargs)
            + octet_length('\\000companies\\000'::bytea)
          )
        ) from test limit 1
      )
    )
  ) - 1
) as foreign_table;

 foreign_table
---------------
 contacts
(1 row)

Obviously, if there's an easier way, I'm all ears. BTW, I would like to
avoid custom functions if at all possible.

Regards,
Ian Morgan
-- 
-------------------------------------------------------------------
 Ian E. Morgan        Vice President & C.O.O.         Webcon, Inc.
 imorgan@webcon.net         PGP: #2DA40D07          www.webcon.net
    *  Customized Linux network solutions for your business  *
-------------------------------------------------------------------





view thread (3+ messages)  latest in thread

Message-ID: <Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net>
Permalink:  ../Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: imorgan@webcon.net
  Subject: Re: How to discover foreign keys (without pulling hair out)
  In-Reply-To: <Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net>
  References: <Pine.LNX.4.44.0204230157280.17139-100000@light.webcon.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox