Thread: unanswered: Schema Issue
I want to extract tables schema information, i've looked at src/bin/psql/describe.c but i cannot determine the datatype 'serial' and 'references' from pg_*, i understand that triggers are generated for serial and references, so how i can understand from my perl application the full schema ? thanks, valter _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
On Thu, 26 Apr 2001, V. M. wrote: > > I want to extract tables schema information, i've looked at > src/bin/psql/describe.c but i cannot determine the datatype > 'serial' and > 'references' from pg_*, i understand that triggers are generated for > serial > and references, so how i can understand from my perl application the > full > schema ? SERIALs are just integers (int4). They don't use a trigger, but use a sequence as a default value. REFERENCES are not a type of data, but a foreign key/primary key relationship. There's still a data type (int, text, etc.) You can derive schema info from the system catalogs. Use psql with -E for examples, or look in the Developer Manual. HTH, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. >From: Joel Burton <jburton@scw.org> >To: "V. M." <txian@hotmail.com> >CC: pgsql-hackers@postgresql.org >Subject: Re: unanswered: Schema Issue >Date: Thu, 26 Apr 2001 13:51:26 -0400 (EDT) > >On Thu, 26 Apr 2001, V. M. wrote: > > > > > I want to extract tables schema information, i've looked at > > src/bin/psql/describe.c but i cannot determine the datatype > > 'serial' and > > 'references' from pg_*, i understand that triggers are generated for > > serial > > and references, so how i can understand from my perl application the > > full > > schema ? > >SERIALs are just integers (int4). They don't use a trigger, but use a >sequence >as a default value. > >REFERENCES are not a type of data, but a foreign key/primary key >relationship. There's still a data type (int, text, etc.) > >You can derive schema info from the system catalogs. Use psql with -E for >examples, or look in the Developer Manual. > >HTH, > >-- >Joel Burton <jburton@scw.org> >Director of Information Systems, Support Center of Washington > _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
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
read it, but i can determine only the related tables and not the fields of these tables that are related. 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.
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.relnameas 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
BYt.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.
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