From: Tim Andersen <timander37@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: About primary keys.
Date: Fri, 15 Aug 2003 14:32:38 -0700 (PDT)
Message-ID: <20030815213238.17559.qmail@web10006.mail.yahoo.com> (raw)
In-Reply-To: <18736.1060963060@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: timander37@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: About primary keys.
In-Reply-To: <20030815213238.17559.qmail@web10006.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox