Question on SQL and pg_-tables - Mailing list pgsql-sql

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       | fbbox        | box      | box  |  32 | f       |    0 |
   
| bbox_box             | f       | fbbox        | box      | box  |  32 | f       |    0 |
   
| bbox_height          | f       | fbbox        | box      | box  |  32 | f       |    0 |
   
| bbox_width           | f       | fbbox        | id       | int4 |   4 | t       |    0 |
nextval('"bbox_id_seq"'::text)  | bbox_pkey            | t       | tbbox        | obj_id   | int4 |   4 | f       |
0|                                  
| bbox_obj_id          | f       | flabel       | descr    | text |  -1 | f       |    0 |
   
|                      |         |label       | id       | int4 |   4 | t       |    0 |
nextval('"label_id_seq"'::text) | label_pkey           | t       | tobj         | id       | int4 |   4 | t       |
0|  
nextval('"obj_id_seq"'::text)   | obj_pkey             | t       | tobj_label_r | label_id | int4 |   4 | f       |
0|                                  
| obj_label_r_label_id | f       | fobj_label_r | obj_id   | int4 |   4 | f       |    0 |
   
| obj_label_r_obj_id   | f       | ftest2       | a        | int4 |   4 | f       |    0 |
   
| test2id              | f       | ftest2       | b        | int4 |   4 | f       |    0 |
   
|                      |         |test2       | c        | int4 |   4 | f       |    0 |
 
| test2id              | f       | ftest2       | 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.attnotnullas notnull,   defj.attndims as dims,   defj.adsrc as default_value,   indj.relname as index,
indj.indisprimaryas 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
getpossible default values  left outer join     pg_attrdef on attrelid = adrelid and attnum = adnum) as defj  -- then
getpossible 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;