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_Name7
from pg_index ind, pg_class relation, pg_class ak, pg_attribute att
where 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)%65536 as 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.attname as 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.attname as 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.oid
and att_pk_0.attnum = co.confkey[1] and att_pk_0.attrelid =
cl_frel.oid;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
