Re: how to save primary key constraints

Hi,

On 12 October 2011 08:16, J.V. <jvsrvcs@gmail.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.ivanic@gmail.com)