replace array values in a select statement

From: Michael Seele <mseele(at)guh-software(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: replace array values in a select statement
Date: 2004-08-24 09:28:24
Message-ID: 412B0A38.6030504@guh-software.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message David Suela Fernández 2004-08-24 09:34:30 problems with pg_dump
Previous Message Ulrich Wisser 2004-08-24 08:27:31 Re: pg_dump in stand alone backend