pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLooking up table names by REFERENCES
2+ messages / 2 participants
[nested] [flat]
* Looking up table names by REFERENCES
@ 2005-01-24 15:09 Steve Castellotti <SteveC@innocent.com>
2005-01-25 19:10 ` Re: Looking up table names by REFERENCES Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Steve Castellotti @ 2005-01-24 15:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Looking up table names by REFERENCES
2005-01-24 15:09 Looking up table names by REFERENCES Steve Castellotti <SteveC@innocent.com>
@ 2005-01-25 19:10 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-01-25 19:10 UTC (permalink / raw)
To: Steve Castellotti <SteveC@innocent.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-01-25 19:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 15:09 Looking up table names by REFERENCES Steve Castellotti <SteveC@innocent.com>
2005-01-25 19:10 ` Michael Fuhr <mike@fuhr.org>
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