If it is for multiple columns' foreign key constraint.
Try this query:
SELECT DISTINCT n.nspname AS from_schema_name, c.relname AS
from_table_name, toSchemaName.nspname AS to_schema_name, toTable.relname
as to_table_name,
fk_col.attname
FROM pg_catalog.pg_class AS c
LEFT JOIN pg_namespace AS
Subject: Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 :
> Hi,
>
> supposing to have a small DB:
>
> TABLE a (
> id SERIAL PRIMARY KEY
> );
>
> TABLE b (
> id SERIAL PRIMARY KEY,
> idA INTEGER NOT NULL REFERENCES a(id)
>
>Well thats it if you use only *single column* Foreign keys.
>In the general case the above will need extra work.
Are you sure? I have tested query with 3 table with multiple FK and
it works (see below) [as you can see
"C" table have two FK to A and B]
test=> SELECT
test-> (SELECT relname FRO
>
>SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1,
pg_class
>c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid;
>
>for column(s) names you will have to do extra homework.
Thanks! I have obtained my query! Here is:
SELECT
(SELECT relname FROM pg_catalog.pg_class WHE
O [EMAIL PROTECTED] έγραψε στις Mar 17, 2006 :
> >
> >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1,
> pg_class
> >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid;
> >
> >for column(s) names you will have to do extra homework.
>
> Thanks! I have obtained my query!
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 :
> > pg_catalog.pg_constraint is your (only?) friend.
>
> I have already examintated this table without results. Seem not to be
> a "human-readable" table :(
Right you will have to join against pg_class,
and make it readable.
SELECT c1.reln
> pg_catalog.pg_constraint is your (only?) friend.
I have already examintated this table without results. Seem not to be
a "human-readable" table :(
TIA
Roberto Colmegna
Tiscali ADSL 4 Mega Flat
Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 :
> Hi,
>
> supposing to have a small DB:
>
> TABLE a (
> id SERIAL PRIMARY KEY
> );
>
> TABLE b (
> id SERIAL PRIMARY KEY,
> idA INTEGER NOT NULL REFERENCES a(id)
> );
>
> How can I inspect pg_schema/information_schema to "detect" the
> rela
Hi,
supposing to have a small DB:
TABLE a (
id SERIAL PRIMARY KEY
);
TABLE b (
id SERIAL PRIMARY KEY,
idA INTEGER NOT NULL REFERENCES a(id)
);
How can I inspect pg_schema/information_schema to "detect" the
relation between "b" and "a" via "idB"?
TIA
Roberto Colmegna
