Re: Looking up table names by REFERENCES

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/