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.tablename and
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.attnum as "colno"
from pg_tables tbls,
pg_class,
pg_attribute cols,
pg_type,
smmtsys.v_datatype,
smmtsys.v_primarykeyind vpk
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
