From: Tilo Schwarz <mail@tilo-schwarz.de>
To: pgsql-sql@postgresql.org
Subject: Question on SQL and pg_-tables
Date: Mon, 25 Nov 2002 17:21:27 +0100
Message-ID: <200211251721.27543.mail@tilo-schwarz.de> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@tilo-schwarz.de
Subject: Re: Question on SQL and pg_-tables
In-Reply-To: <200211251721.27543.mail@tilo-schwarz.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox