Dear all,
after reading about the pg_* system tables, I made up a view to see the all
user columns, their type, default value, indices etc. at once (see example
below). Now my first question is:
- Is it possible to get the same result with a simpler / shorter SQL query
than shown below (I'm not so familiar with SQL yet, hopefully that query is
correct in the first place...)?
I was also trying to get the RI-Constraints out of the pg_* tables. I found
the two tables involved in a RI-Constraint in pg_trigger (tgrelid,
tgconstrrelid), but the affected columns are only(?) in the tgargs. To get
them out of tgargs, I need some (easy) string processing. My second question
is:
- Is it possible to get not only the two tables, but also their corresponding
two columns involved in a RI-Constraint out of the pg_* tables just with a
SQL query?
Thanks for any comments!
Tilo
Example and view definition for question one:
Example:
testobj=> select * from columns;
table | column | type | len | notnull | dims | default_value
| index | primary | unique
-------------+----------+------+-----+---------+------+---------------------------------+----------------------+---------+--------
bbox | box | box | 32 | f | 0 |
| bbox_area | f | f
bbox | box | box | 32 | f | 0 |
| bbox_box | f | f
bbox | box | box | 32 | f | 0 |
| bbox_height | f | f
bbox | box | box | 32 | f | 0 |
| bbox_width | f | f
bbox | id | int4 | 4 | t | 0 |
nextval('"bbox_id_seq"'::text) | bbox_pkey | t | t
bbox | obj_id | int4 | 4 | f | 0 |
| bbox_obj_id | f | f
label | descr | text | -1 | f | 0 |
| | |
label | id | int4 | 4 | t | 0 |
nextval('"label_id_seq"'::text) | label_pkey | t | t
obj | id | int4 | 4 | t | 0 |
nextval('"obj_id_seq"'::text) | obj_pkey | t | t
obj_label_r | label_id | int4 | 4 | f | 0 |
| obj_label_r_label_id | f | f
obj_label_r | obj_id | int4 | 4 | f | 0 |
| obj_label_r_obj_id | f | f
test2 | a | int4 | 4 | f | 0 |
| test2id | f | f
test2 | b | int4 | 4 | f | 0 |
| | |
test2 | c | int4 | 4 | f | 0 |
| test2id | f | f
test2 | d | int4 | 4 | f | 0 |
| | |
test2 | e | int4 | 4 | f | 0 |
| test2id | f | f
(16 rows)
View definition:
CREATE VIEW columns as
select
defj.relname as table,
defj.attname as column,
defj.typname as type,
defj.attlen as len,
defj.attnotnull as notnull,
defj.attndims as dims,
defj.adsrc as default_value,
indj.relname as index,
indj.indisprimary as primary,
indj.indisunique as unique
from
-- first get all user columns for all user tables
((select * from
pg_class,
pg_attribute,
pg_type
where
pg_class.oid = attrelid
and pg_type.oid = atttypid
and relname !~ 'pg_'
and relname !~ 'pga_'
and pg_class.relkind = 'r'
and pg_attribute.attnum > 0) as colj
-- then get possible default values
left outer join
pg_attrdef on attrelid = adrelid and attnum = adnum) as defj
-- then get possible indices
left outer join
(select * from
pg_class,
pg_index,
pg_attribute
where
pg_class.oid = indexrelid
and pg_class.oid = attrelid) as indj
on (defj.attrelid = indj.indrelid
and defj.attnum = indj.indkey[indj.attnum-1])
order by
1,
2,
index;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
