Re: how to save primary key constraints

Hi,

On 12 October 2011 08:16, J.V. <jvsrvcs(at)gmail(dot)com> wrote:
> I need to be able to query for all primary keys and save the table name and
> the name of the primary key field into some structure that I can iterate
> through later.

psql -E is your friend here. Then use \d <table> and you get several
internal queries like this:

SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(queue)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
oid | nspname | relname
-------+---------+---------
26732 | public | queue

SELECT conname, conrelid::pg_catalog.regclass,
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '26732' AND c.contype = 'f' ORDER BY 1;
conname | conrelid |
condef
-----------------------------------+------------------------+------------------------------------------
T_fkey | T | FOREIGN KEY (queue) REFERENCES queue(id)
...

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)