Analyzing the 7.3 SQL92 Schema - Mailing list pgsql-sql

I needed theses Statements, and i think tey could be usefull to
others.
I hate the non-oracle outer joins, so i implemented the the nullable
fields wit a subselect. If someone will rewrite that, please mail the
new statements to c.gausepohl(at)arcusx.com

so here comes the script:
--list of tables for user loged on
select tablename from pg_tables
where tableowner=user
order by tablename;


--primary keys from table xyz?
select cl.relname, co.conname, apk.attname as pk_attribute_0,      (select attname from pg_attribute where
attnum=co.conkey[2]and
 
attrelid=cl.oid) as pk_attribute_1,      (select attname from pg_attribute where attnum=co.conkey[3] and
attrelid=cl.oid) as pk_attribute_2,      (select attname from pg_attribute where attnum=co.conkey[4] and
attrelid=cl.oid) as pk_attribute_3,      (select attname from pg_attribute where attnum=co.conkey[5] and
attrelid=cl.oid) as pk_attribute_4,      (select attname from pg_attribute where attnum=co.conkey[6] and
attrelid=cl.oid) as pk_attribute_5,      (select attname from pg_attribute where attnum=co.conkey[7] and
attrelid=cl.oid) as pk_attribute_6,      (select attname from pg_attribute where attnum=co.conkey[8] and
attrelid=cl.oid) as pk_attribute_7
from   pg_constraint co, pg_class cl, pg_attribute apk
where  contype = 'p' and  cl.oid = co.conrelid and cl.relname = 'xyz' and apk.attrelid=cl.oid and apk.attnum =
co.conkey[1];
 
--alternate keys from table xyz?
select ak.relname,         att.attname as Column_Name0,         (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[1]) as Column_Name1,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[2]) as Column_Name2,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[3]) as Column_Name3,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[4]) as Column_Name4,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[5]) as Column_Name5,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[6]) as Column_Name6,       (SELECT attname FROM pg_attribute WHERE attrelid =
relation.oid  and attnum=ind.indkey[7]) as Column_Name7from   pg_index ind, pg_class relation, pg_class ak,
pg_attributeattwhere  ind.indrelid=relation.oid    and  relation.relname = 'xyz'  and  ind.indisprimary = 'f'    and
ind.indisunique= 't'    and  ind.indexrelid = ak.oid   and  att.attnum = ind.indkey[0] and att.attrelid = relation.oid
 

--detailed list for columns of table xyz?
select att.attname as column_name,       att.attnotnull as nullable,      typ.typname as data_type,       att.attlen,
   typ.typrelid,      att.atttypmod-4 as data_length,      (att.atttypmod-4)/65535 as data_precision,
(att.atttypmod-4)%65536as data_scale,      d.description as description,      att.attnum as column_id
 
from   pg_attribute att, pg_class cls, pg_type typ, pg_description d
where  d.objoid = cls.oid and  att.attrelid = cls.oid  and  typ.oid = att.atttypid and  att.attnum>=1 and  cls.relname
='xyz'
 
order by cls.relname, att.attnum;  


--out references for table xyz
select rel.relname as table_name, fk.conname as fk_constraint_name,
frel.relname as target_table_name,      fk.conkey[1], fk.confkey[1],      att_fk0.attname as fk_part_0,
att_pk0.attnameas pk_part_0,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
 
rel.oid and att.attnum=fk.conkey[2]) as fk_part_1,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[2]) as pk_part_1,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[3]) as fk_part_2,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[3]) as pk_part_2,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[4]) as fk_part_3,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[4]) as pk_part_3,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[5]) as fk_part_4,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[5]) as pk_part_4,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[6]) as fk_part_5,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[6]) as pk_part_5,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[7]) as fk_part_6,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[7]) as pk_part_6,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid
=
rel.oid and att.attnum=fk.conkey[8]) as fk_part_7,      (SELECT att.attname FROM pg_attribute att WHERE att.attrelid =
frel.oid and att.attnum=fk.confkey[8]) as pk_part_7
from pg_constraint fk, pg_class rel, pg_class frel,    pg_attribute att_fk0, pg_attribute att_pk0
where fk.contype='f' and rel.oid = fk.conrelid and frel.oid = fk.confrelid and rel.relname = 'xyz' and att_fk0.attrelid
=rel.oid and att_fk0.attnum=fk.conkey[1] and att_pk0.attrelid = rel.oid and att_pk0.attnum=fk.confkey[1]
 
order by table_name, target_table_name;


--in reference for table xyz...
select cl_rel.relname as table_name,       co.conname,       cl_frel.relname as target_table_name,
att_fk_0.attnameas fk_part_0,      att_pk_0.attname as pk_part_0,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[2]) as fk_part_1,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[2]) as pk_part_1,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[3]) as fk_part_2,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[3]) as pk_part_2,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[4]) as fk_part_3,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[4]) as pk_part_3,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[5]) as fk_part_4,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[5]) as pk_part_4,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[6]) as fk_part_5,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[6]) as pk_part_5,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[7]) as fk_part_6,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[7]) as pk_part_6,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_rel.oid and att.attnum = co.conkey[8]) as fk_part_7,      (select att.attname from pg_attribute att where
att.attrelid=
 
cl_frel.oid and att.attnum = co.confkey[8]) as pk_part_7
from pg_constraint co, pg_class cl_rel, pg_class cl_frel, pg_attribute
att_fk_0, pg_attribute att_pk_0
where co.conrelid = cl_rel.oid and co.confrelid = cl_frel.oid and cl_frel.relname = 'acc_account_category'and
att_fk_0.attnum= co.conkey[1] and att_fk_0.attrelid = cl_rel.oidand att_pk_0.attnum = co.confkey[1] and
att_pk_0.attrelid=
 
cl_frel.oid;