pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedR: Re: R: R: Re: schema inspection
3+ messages / 3 participants
[nested] [flat]
* R: Re: R: R: Re: schema inspection
@ 2006-03-17 08:56 rcolmegna@tiscali.it <rcolmegna@tiscali.it>
0 siblings, 1 reply; 3+ messages in thread
From: rcolmegna@tiscali.it @ 2006-03-17 08:56 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
>
>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 WHERE oid=conrelid) AS
fromTbl,
(SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS
toTbl,
(SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid
AND conkey[1]=attnum) AS viaCol
FROM pg_catalog.pg_constraint AS rel WHERE contype='f';
TIA
Roberto Colmegna
Tiscali ADSL 4 Mega Flat
Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese!
Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE.
http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscal...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: R: Re: R: R: Re: schema inspection
@ 2006-03-17 10:21 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: rcolmegna@tiscali.it <rcolmegna@tiscali.it>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleus Mantzios @ 2006-03-17 10:21 UTC (permalink / raw)
To: rcolmegna@tiscali.it <rcolmegna@tiscali.it>; +Cc: pgsql-sql
O rcolmegna@tiscali.it Ýãñáøå óôéò 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! Here is:
>
> SELECT
> (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS
> fromTbl,
> (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS
> toTbl,
> (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid
> AND conkey[1]=attnum) AS viaCol
> FROM pg_catalog.pg_constraint AS rel WHERE contype='f';
Well thats it if you use only *single column* Foreign keys.
In the general case the above will need extra work.
Of course you will also have to ensure that the constraint is indeed
a FK constraint, that the column is not droped, etc....
which leads to the answer that enabling statement logging,
and then \d and watching the log is a very good friend too.
>
> TIA
> Roberto Colmegna
>
>
>
>
> Tiscali ADSL 4 Mega Flat
> Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 ⬠al mese!
> Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE.
> http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscal...
>
--
-Achilleus
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: R: Re: R: R: Re: schema inspection
@ 2006-05-12 20:57 Emi Lu <emilu@encs.concordia.ca>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Emi Lu @ 2006-05-12 20:57 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: rcolmegna@tiscali.it <rcolmegna@tiscali.it>; pgsql-sql
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 n ON (n.oid = c.relnamespace)
INNER JOIN pg_catalog.pg_constraint AS rel ON (c.oid=rel.conrelid)
LEFT JOIN pg_catalog.pg_class AS toTable ON (toTable.oid = rel.confrelid)
LEFT JOIN pg_namespace AS toSchemaName ON (toSchemaName.oid =
toTable.relnamespace)
LEFT JOIN pg_catalog.pg_attribute AS fk_col ON fk_col.attrelid =
rel.conrelid AND (position(fk_col.attnum in array_to_string(conkey, '
')) <>0 )
WHERE rel.contype='f'
ORDER BY from_schema_name, from_table_name;
Ying
>O rcolmegna@tiscali.it έγραψε στις 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! Here is:
>>
>>SELECT
>> (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS
>>fromTbl,
>> (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS
>>toTbl,
>> (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid
>>AND conkey[1]=attnum) AS viaCol
>>FROM pg_catalog.pg_constraint AS rel WHERE contype='f';
>>
>>
>
>Well thats it if you use only *single column* Foreign keys.
>In the general case the above will need extra work.
>
>Of course you will also have to ensure that the constraint is indeed
>a FK constraint, that the column is not droped, etc....
>which leads to the answer that enabling statement logging,
>and then \d and watching the log is a very good friend too.
>
>
>
>>TIA
>>Roberto Colmegna
>>
>>
>>
>>
>>Tiscali ADSL 4 Mega Flat
>>Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 � al mese!
>>Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE.
>>http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscal...
>>
>>
>>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-12 20:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-17 08:56 R: Re: R: R: Re: schema inspection rcolmegna@tiscali.it <rcolmegna@tiscali.it>
2006-03-17 10:21 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2006-05-12 20:57 ` Emi Lu <emilu@encs.concordia.ca>
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