Message-ID: <20030815143853.64952.qmail@web10009.mail.yahoo.com>
Date: Fri, 15 Aug 2003 07:38:53 -0700 (PDT)
From: Tim Andersen <timander37@yahoo.com>
Subject: Re: About primary keys.
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

I'm new to PostgreSQL but I am familiar with DB2,
Oracle and Sybase.  I must say, I am impressed with
PostgreSQL so far!

In order to compare databases across DBMS platforms,
we need to create a view that queries from the system
catalog tables.  This view returns all of the columns
in the database listed by schema, table, and
columnname with some additional information about the
column (such as a primary key indicator).

These are the columns in the view:
creator (schema), tname (tablename), cname
(columnname), coltype (datatype), nulls (nullable),
length, syslength (precision), in_primary_key, colno
(columnumber), default_value, comments

I looked in the archives at postgresql.com, and I
found someone else with the same problem that I had
but no solution was posted.

I have made some good progress on creating a view that
selects from system catalog tables, but I am having
trouble with the in_primary_key and the
length/precision columns.  Many of our tables have
complex primary keys.

The query I have so far only gets columns that are
part of a primary key.  I need to return all of the
columns listed and a Y/N indicator for whether or not
the column is a part of the tables primary key.
Here's what I have:
/*-------------------------------//
// This view shows all rows that //
// are part of a primary key:    //
//-------------------------------*/
select upper(pgt1.schemaname) as "creator",
       upper(pgt1.tablename) as "tname",
       upper(pga1.attname) as "cname",
       case smmtsys.v_datatype.typname
         when 'bpchar' then 'char'
         else smmtsys.v_datatype.typname
       end as "coltype",
       case pga1.attnotnull
         when true then 'N'
         when false then 'Y'
       end as "nulls",
       i.indisprimary as "in_primary_key",
       pga1.atttypmod as "length",
       pga1.attndims as "syslength",
       pga1.attnum as "colno"
  from pg_tables pgt1,
       pg_class pgc1,
       pg_attribute pga1,
       pg_attribute pga2,
       pg_type,
       smmtsys.v_datatype,
       pg_index i,
       pg_namespace n
 where pgc1.relname = pgt1.tablename
       and pg_type.typname = pgt1.tablename
       and pga1.attrelid = pgc1.relfilenode
       and  pga1.attnum > 0
       and pga1.atttypid = smmtsys.v_datatype.oid
       and pgc1.oid = i.indrelid
       and i.indisprimary = 't'
       and n.oid = pgc1.relnamespace
       and pgt1.tablename = pgc1.relname
       and pga2.attrelid = i.indexrelid
       and pga1.attrelid = i.indrelid
       and pga1.attnum = i.indkey[pga2.attnum-1];

/*---------------------------//
// this is a quick and dirty //
// view to get the datatypes //
// used in the above query:  //
//---------------------------*/ 
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;


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