Re: About primary keys. - Mailing list pgsql-sql

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.indisprimaryas "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 nwhere 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