hi!
i need a list of all primary keys in my database! for this reson i build
these sql-statement:
/SELECT
pg_class.relnam e AS tableName,
pg_constraint.c onname AS pkName,
pg_constraint.c onkey AS columns
FROM
pg_catalog.pg_c onstraint
INNER JOIN pg_catalog.pg_c lass ON pg_constraint.c onrelid = pg_class.oid
WHERE
pg_constraint.c ontype = '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.relnam e,
pg_attribute.at tname,
pg_attribute.at tnum
FROM
pg_catalog.pg_a ttribute
INNER JOIN pg_catalog.pg_c lass ON pg_attribute.at trelid =
pg_class.oid
WHERE
pg_class.relnam e in ('employee','cu stomer','part')
AND
pg_attribute.at tnum = 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 Softwareentwick lung 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