Re: Script para saber claves foraneas sobre una tabla

Javier Chávez B. escribió:
> Estimados :
>
> Existe alguna manera de saber mediante un script sobre las tablas de sistema
> que tablas hacen referencia a una tabla, o sea saber donde el identificador
> de una tabla en particular esta siendo utilizada como llave foranea por
> otras...

Por supuesto ... incluso es una nueva característica de psql en 8.4:

psql (8.4.0)
Digite «help» para obtener ayuda.

alvherre=# create table foo (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «foo_pkey» para l
a tabla «foo»
CREATE TABLE
alvherre=# create table bar (a int not null references foo);
CREATE TABLE

alvherre=# \d+ foo
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(foo)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
, c.reltablespace
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '16395'

**************************

********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum, a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16395' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '16395' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
**************************

********* QUERY **********
SELECT conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16395' AND r.contype = 'f' ORDER BY 1
**************************

********* QUERY **********
SELECT conname, conrelid::pg_catalog.regclass,
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '16395' AND c.contype = 'f' ORDER BY 1
**************************

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid), t.tgenabled
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '16395' AND t.tgconstraint = 0
ORDER BY 1
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16395' ORDER BY inhseqno
**************************

Tabla «public.foo»
Columna | Tipo | Modificadores | Almacenamiento | Descripción
---------+---------+---------------+----------------+-------------
a | integer | not null | plain |
Índices:
"foo_pkey" PRIMARY KEY, btree (a)
Referenciada por:
TABLE "bar" CONSTRAINT "bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
Tiene OIDs: no

--
Alvaro Herrera http://planet.postgresql.org/
"La gente vulgar sólo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"