Re: About primary keys -- made some progress - Mailing list pgsql-sql

This might not be the cleanest solution, but it runs
fast and it retrieved the information I need.
I broke it down into pieces and created several views
to query from to simplify it for myself.
The first four statements are views and the last one
is the query I was originally trying to get.  (note
that smmtsys is a schema I created, everything else is
dealing with system catalog tables)
Here's the SQL:
---------------
create view smmtsys.v_datatype as (
select oid, typname from pg_type)
;

create view smmtsys.v_primarykeys as(
select pg_tables.schemaname, pg_tables.tablename,
ta.attname, ta.attrelid, 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
AND (pg_tables.schemaname = 'summit' or 
pg_tables.schemaname = 'uhelp' or 
pg_tables.schemaname = 'smmtsys' or 
pg_tables.schemaname = 'smmtsec' or 
pg_tables.schemaname = 'smmtccon' )
and ta.attname > 0
)
;

create view smmtsys.v_allcolumns as (
select pg_tables.schemaname,      pg_tables.tablename,      pg_attribute.attname
from pg_tables,     pg_class,     pg_attribute,     smmtsys.v_datatype 
where (schemaname = 'smmtccon' or       schemaname = 'smmtsec' or       schemaname = 'smmtsys' or       schemaname =
'summit'or       schemaname = 'uhelp' ) and      pg_class.relname = pg_tables.tablename and      pg_type.typname =
pg_tables.tablenameand      pg_attribute.attrelid = pg_class.relfilenode and
 
     pg_attribute.attnum > 0 and      pg_attribute.atttypid = smmtsys.v_datatype.oid
)
;

create view smmtsys.v_primarykeyind as (
select cols.schemaname ,      cols.tablename ,      cols.attname,      case pks.indisprimary        when true then 'Y'
     else 'N'      end as in_primary_key
 
from smmtsys.v_allcolumns cols left outer join
smmtsys.v_primarykeys pks
on (cols.schemaname = pks.schemaname   and cols.tablename = pks.tablename   and cols.attname= pks.attname)
);

select upper(tbls.schemaname) as "creator",       upper(tbls.tablename) as "tname",       upper(cols.attname) as
"cname",      case smmtsys.v_datatype.typname        when 'bpchar' then 'char'        else smmtsys.v_datatype.typname
  end as "coltype",       case cols.attnotnull        when true then 'N'        when false then 'Y'      end as
"nulls",     length(cols.attrelid) as "length",        cols.attndims as "syslength",       vpk.in_primary_key,
cols.attnumas "colno"
 
from pg_tables tbls,     pg_class,     pg_attribute cols,     pg_type,     smmtsys.v_datatype,
smmtsys.v_primarykeyindvpk
 
where (tbls.schemaname = 'smmtccon'      or tbls.schemaname = 'smmtsec'      or tbls.schemaname = 'smmtsys'      or
tbls.schemaname= 'summit'      or tbls.schemaname = 'uhelp')     and pg_class.relname = tbls.tablename     and
pg_type.typname= tbls.tablename     and cols.attrelid = pg_class.relfilenode     and cols.attnum > 0     and
cols.atttypid= smmtsys.v_datatype.oid     and vpk.schemaname = tbls.schemaname     and vpk.tablename = tbls.tablename
 and vpk.attname = cols.attname
 
;

This retrieves all of the columns and shows a primary
key indicator for each column.  If someone could put
this logic all into one SQL query, I'd really like to
see it!

I still have a question about how to get the
information about length and precision of a column
from pg_attributes.atttypmod.  are there built-in
functions for PostgreSQL to extract this information?
Additionally, I need to get the column default value
and the comments on the column, but I think I can
figure that out with a little more time.



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