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.attnumfrom pg_attribute ta, pg_attribute ia, pg_class c,
pg_index i, pg_namespace nwhere c.oid = i.indrelidAND n.oid = c.relnamespaceAND i.indisprimary = 't'AND ia.attrelid =
i.indexrelidANDta.attrelid = i.indrelidAND 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
