pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to discover foreign keys (without pulling hair out)
3+ messages / 2 participants
[nested] [flat]

* How to discover foreign keys (without pulling hair out)
@ 2002-04-23 06:54 Ian Morgan <imorgan@webcon.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Ian Morgan @ 2002-04-23 06:54 UTC (permalink / raw)
  To: pgsql-sql

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  *
-------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to discover foreign keys (without pulling hair out)
@ 2002-04-25 12:27 bob lapique <lapique@chez.com>
  2002-04-25 17:04 ` Re: How to discover foreign keys (without pulling hair out) Ian Morgan <imorgan@webcon.net>
  0 siblings, 1 reply; 3+ messages in thread

From: bob lapique @ 2002-04-25 12:27 UTC (permalink / raw)
  To: imorgan@webcon.net; +Cc: pgsql-sql

Hi,

I use pgAdminII on Windows. It connects to a PostgreSQL
on a Linux PC.
When you click on an existing table's icon, it shows you
a script that could have created it (it detects the
structure of the table by itself), including the foreign
keys.
That's an easy way. But maybe not very elegant...

-- Table: slip
CREATE TABLE "slip" (
  "id_slip" int4 DEFAULT nextval
('"slip_id_slip_seq"'::text) NOT NULL,
  "proprio" int4,
  CONSTRAINT "slip_pkey" PRIMARY KEY ("id_slip"),
  CONSTRAINT "slip_de" FOREIGN KEY ("proprio")
REFERENCES "maitre" ("maitre_id") ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
COMMENT ON TABLE "slip" IS 'les slobards en folie !';
-------------------
Vous aussi devenez le "Coup de coeur de Julie" et gagnez une Webcam
sur http://www.chez.com !
Chez.com vous invite à découvrir l'univers passionnant des sites persos.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to discover foreign keys (without pulling hair out)
  2002-04-25 12:27 Re: How to discover foreign keys (without pulling hair out) bob lapique <lapique@chez.com>
@ 2002-04-25 17:04 ` Ian Morgan <imorgan@webcon.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Ian Morgan @ 2002-04-25 17:04 UTC (permalink / raw)
  To: bob lapique <lapique@chez.com>; +Cc: pgsql-sql

On Thu, 25 Apr 2002, bob lapique wrote:

> I use pgAdminII on Windows. It connects to a PostgreSQL on a Linux PC.
> When you click on an existing table's icon, it shows you a script that
> could have created it (it detects the structure of the table by itself),
> including the foreign keys. That's an easy way. But maybe not very
> elegant...

I have in fact tried that very thing. pgAdminII has a Log View that is
supposed to show all the back-end SQL that it executes in order to show you
what it does. However, all it really does is retrieve the pg_triggers.tgargs
field, which is 6 fields separated by \0's, then it must parse it internally.

I'm looking for a way to parse 'one\000two\000three\000'::bytea into

 foo1 | foo2 | foo3
------+------+-------
 one  | two  | three

using only (Postgre)SQL, no external C functions or the like. Ideas? I'm
looking into PL/pgSQL as a solution, but haven't gotten very far yet.


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  *
-------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-04-25 17:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-23 06:54 How to discover foreign keys (without pulling hair out) Ian Morgan <imorgan@webcon.net>
2002-04-25 12:27 Re: How to discover foreign keys (without pulling hair out) bob lapique <lapique@chez.com>
2002-04-25 17:04 ` Ian Morgan <imorgan@webcon.net>

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