Message-ID: <20030815213238.17559.qmail@web10006.mail.yahoo.com>
Date: Fri, 15 Aug 2003 14:32:38 -0700 (PDT)
From: Tim Andersen <timander37@yahoo.com>
Subject: Re: About primary keys. 
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <18736.1060963060@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

I looked in the info.c on line 2891 of the
psqlodbc-7.2.5 to find this SQL logic (courtesy of Tom
Lane)
select ta.attname, ia.attnum
 from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n
 where c.oid = i.indrelid
 AND n.oid = c.relnamespace
 AND i.indisprimary = 't'
 AND ia.attrelid = i.indexrelid
 AND ta.attrelid = i.indrelid
 AND ta.attnum = i.indkey[ia.attnum-1];

The above SQL retrieves each and every column in the
database that is a part of a complex primary key.
I need to join this to a list of all of the columns in
the database so I can have the primary key indicator. 


Here's another variation of the above SQL that shows
schema, table, column, colum_num, and a primary key
indicator:

select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ia.attnum, i.indisprimary
from pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace n , pg_tables
where c.oid = i.indrelid
AND n.oid = c.relnamespace
AND i.indisprimary = 't'
AND ia.attrelid = i.indexrelid
AND ta.attrelid = i.indrelid
AND ta.attnum = i.indkey[ia.attnum-1]
AND pg_tables.tablename = c.relname;

so, shouldn't there be an easy way to retrieve all of
the columns for all tables with a primary key
indicator using this strategy?

If creating another view will simplify syntax, that's
fine too.


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com