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)
