Query FKey Constraint Table and Column Names

Поиск
Список
Период
Сортировка
От Ian Harding
Тема Query FKey Constraint Table and Column Names
Дата
Msg-id 3F798019.1000209@tpchd.org
обсуждение исходный текст
Ответы Re: Query FKey Constraint Table and Column Names  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I need to find out which tables, and columns in that table, reference a
given table.

I would like to use the information_schema views, but am stuck at 7.3.X
right now.

Here is what I came up with so far
SELECT pcl.relname AS thistable,
    pcl1.relname AS referencesthistable
FROM ((pg_constraint pc JOIN pg_class pcl
    ON ((pc.conrelid = pcl.oid)))
    JOIN pg_class pcl1 ON ((pc.confrelid = pcl1.oid)));

which is fine as far as it goes.  What is not readily apparent is how to
get the column names.  I see there are conkey and confkey in
pg_constraint, which seem to be arrays of integers that might just
relate to attnum in pg_attribute.

Is there already a view/function that will get me this info, and if not,
am I barking up the right tree here?  I seldom tinker in system tables,
because I am too lazy to learn them, and because it seems to be bad
form.  They are none of my business, really.

Looking forward to the information_schema crutch!

Ian



В списке pgsql-general по дате отправления:

Предыдущее
От: psql-mail@freeuk.com
Дата:
Сообщение: Slow SELECT
Следующее
От: Kris Jurka
Дата:
Сообщение: Re: RE : mod_auth_pgsql 2.0.1 don't close the backend