[SQL] Looking up table names by REFERENCES

[Steve Castellotti]

    Hello all. I'm trying to write a recursive procedure to allow me to handle some data backup routines and deal with conflicts when restoring from within the program.

    Essentially, what I'd like to be able to do is if 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 images in the database, I would need to backup all of the information about the file(s) each image corresponds to.

    For instance, I can get a list of all (user) table names with:

SELECT relname AS table_name, oid
FROM pg_class
WHERE NOT relname ~ 'pg_.*'
AND NOT relname ~ 'pga_.*'
AND NOT relname ~ '.*_pkey'
AND NOT relname ~ '.*_id_key'
ORDER BY relname;

and I can get a list of column names and their types (for the "image" table) with:

SELECT a.attname AS field, t.typname AS type
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'image' and a.attnum > 0
and a.attrelid = c.oid and a.atttypid = t.oid
ORDER BY a.attnum;


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


    Any help would be most appreciated, especially if I could be cc'd directly.


Cheers

Steve Castellotti