Thread: Looking up table names by REFERENCES

<br />     Hello all. I'm trying to write a recursive procedure to allow me to handle some data backup routines and
dealwith conflicts when restoring from within the program.<br /><br />     Essentially, what I'd like to be able to do
isif a table called "image" has a column called "file_id" which references a column called "file_id" in another table
called"file" I want to be able to determine that pragmatically. If I wanted to backup all of the information about
imagesin the database, I would need to backup all of the information about the file(s) each image corresponds to.<br
/><br/>     For instance, I can get a list of all (user) table names with:<br /><br /> SELECT relname AS table_name,
oid<br/> FROM pg_class<br /> WHERE NOT relname ~ 'pg_.*'<br /> AND NOT relname ~ 'pga_.*'<br /> AND NOT relname ~
'.*_pkey'<br/> AND NOT relname ~ '.*_id_key'<br /> ORDER BY relname;<br /><br /> and I can get a list of column names
andtheir types (for the "image" table) with:<br /><br /> SELECT a.attname AS field, t.typname AS type <br /> FROM
pg_classc, pg_attribute a, pg_type t <br /> WHERE c.relname = 'image' and a.attnum > 0 <br /> and a.attrelid = c.oid
anda.atttypid = t.oid <br /> ORDER BY a.attnum;<br /><br /><br />     Surely there's a simple way I can trace
REFERENCESin a particular column across tables?<br /><br /><br />     Any help would be most appreciated, especially if
Icould be cc'd directly.<br /><br /><br /> Cheers<br /><br /> Steve Castellotti  

On Tue, Jan 25, 2005 at 04:09:09AM +1300, Steve Castellotti wrote:

>     Surely there's a simple way I can trace REFERENCES in a particular
> column across tables?

The pg_constraint table contains foreign key constraints.  Here's
an example query that appears to work in trivial tests:

SELECT c.conname,      c.conrelid::regclass, a1.attname,      c.confrelid::regclass, a2.attname AS fattname
FROM pg_constraint AS c
JOIN pg_attribute AS a1 ON a1.attrelid = c.conrelid AND a1.attnum = ANY (c.conkey)
JOIN pg_attribute AS a2 ON a2.attrelid = c.confrelid AND a2.attnum = ANY (c.confkey)
WHERE c.contype = 'f';

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/