Looking up table names by REFERENCES
От | Steve Castellotti |
---|---|
Тема | Looking up table names by REFERENCES |
Дата | |
Msg-id | 1106579349.746.8.camel@odyssey обсуждение исходный текст |
Ответы |
Re: Looking up table names by REFERENCES
|
Список | pgsql-sql |
<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
В списке pgsql-sql по дате отправления: