replace array values in a select statement

Поиск
Список
Период
Сортировка
От Michael Seele
Тема replace array values in a select statement
Дата
Msg-id 412B0A38.6030504@guh-software.de
обсуждение исходный текст
Список 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 

В списке pgsql-general по дате отправления:

Предыдущее
От: Ulrich Wisser
Дата:
Сообщение: Re: pg_dump in stand alone backend
Следующее
От: David Suela Fernández
Дата:
Сообщение: problems with pg_dump