Thread

1. Re: Re: unanswered: Schema Issue

   V. M. <txian@hotmail.com> — 2001-04-26T19:24:02Z

   perhaps adding  t.tgargs to your view enable me to extract parameters
   that are the related fields
   ---------------------------------------
   
   
   CREATE VIEW dev_ri
                   AS
                   SELECT  ***** t.tgargs **** , t.oid as trigoid,
                       c.relname as trig_tbl,
                       t.tgfoid,
                       f.proname as trigfunc,
                       t.tgenabled,
                       t.tgconstrname,
                       c2.relname as const_tbl,
                       t.tgdeferrable,
                       t.tginitdeferred
                       FROM pg_trigger t,
                       pg_class c,
                       pg_class c2,
                       pg_proc f
                       WHERE t.tgrelid=c.oid
                       AND t.tgconstrrelid=c2.oid
                       AND tgfoid=f.oid
                       AND tgname ~ '^RI_'
                       ORDER BY t.oid;
   
   
   a tgargs example is:
   
   fk_provincie_id_paesi_id_provin\000paesi\000province\000UNSPECIFIED\000id_provincia\000id\000
   
   first field (fk_provincie_id_paesi_id_provin) is constraint name, and i can 
   understand that:  paesi(id_provincia) references provincia(id).
   
   valter
   
   
   
   
   >From: Joel Burton <jburton@scw.org>
   >To: "V. M." <txian@hotmail.com>
   >CC: pgsql-hackers@postgresql.org
   >Subject: [HACKERS] Re: unanswered: Schema Issue
   >Date: Thu, 26 Apr 2001 14:42:31 -0400 (EDT)
   >
   >On Thu, 26 Apr 2001, V. M. wrote:
   >
   > > ok for  serials, now i can extract from psql (\d tablename).
   > >
   > > But i'm not able to extract foreign keys from the schema.
   >
   >Yes you can. Read my tutorial on Referential Integrity in the top section
   >at techdocs.postgresql.org.
   >
   >--
   >Joel Burton   <jburton@scw.org>
   >Director of Information Systems, Support Center of Washington
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 4: Don't 'kill -9' the postmaster
   
   _________________________________________________________________________
   Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
   
   
   

2. Re: [HACKERS] Re: unanswered: Schema Issue

   Joel Burton <jburton@scw.org> — 2001-04-26T19:32:47Z

   On Thu, 26 Apr 2001, V. M. wrote:
   
   (moving this conversation back to pgsql-general, followups to there)
   
   > perhaps adding  t.tgargs to your view enable me to extract parameters
   > that are the related fields
   
   At SCW, we use a naming convention for RI triggers, to allow
   us to easily extract that, and deal with error messages.
   
   We use:
   
   CREATE TABLE p (id INT);
   
   CREATE TABLE c (id INT CONSTRAINT c__ref_id REFERENCES p);
   
   This allows us at a glance to see in error messages what field of what
   table we were referencing. In an Access front end, we can trap this
   error message to a nice statement like "You're trying to change a value in
   the table "c", using information in table "p", "id", but...")
   
   If you don't have this, yes, you can look at in
   the tgargs, but, given that its a bytea field, it's hard to
   programmatically dig anything out of it.
   
   HTH,
   -- 
   Joel Burton   <jburton@scw.org>
   Director of Information Systems, Support Center of Washington