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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
