Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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 

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group