hi!
i need a list of all primary keys in my database! for this reson i build these sql-statement:
SELECT
pg_class.relname AS tableName,
pg_constraint.conname AS pkName,
pg_constraint.conkey AS columns
FROM
pg_catalog.pg_constraint
INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
pg_constraint.contype = 'p'
my result is:
tablename pkname columns
------------ ------------- ----------
customer customer_pkey {1}
employee employee_pkey {1}
part part_pkey {1}
i write a second statement to get the column-names for the column-numbers:
SELECT
pg_class.relname,
pg_attribute.attname,
pg_attribute.attnum
FROM
pg_catalog.pg_attribute
INNER JOIN pg_catalog.pg_class ON pg_attribute.attrelid = pg_class.oid
WHERE
pg_class.relname in ('employee','customer','part')
AND
pg_attribute.attnum = 1 my result is:
relname attname attnum
---------- ----------- ---------
customer customer_id 1
employee employee_id 1
part part_id 1 is it possible to replace the columnNumbers in the colums array of the first statement with the real column-names of the second statement i one big select statement?
this should be my result:
tablename pkname columns
------------ ------------- ----------
customer customer_pkey {customer_id}
employee employee_pkey {employee_id}
part part_pkey {part_id}
--
G & H Softwareentwicklung GmbH Tel.: +49(0)7451/53706-20
Robert-Bosch-Str. 23 Fax: +49(0)7451/53706-90
D-72160 Horb a.N. http://www.guh-software.de
