pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on SQL and pg_-tables
4+ messages / 3 participants
[nested] [flat]
* Question on SQL and pg_-tables
@ 2002-11-25 16:21 Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-25 18:32 ` Re: Question on SQL and pg_-tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tilo Schwarz @ 2002-11-25 16:21 UTC (permalink / raw)
To: 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 | 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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question on SQL and pg_-tables
2002-11-25 16:21 Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
@ 2002-11-25 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-27 10:13 ` Re: Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-11-25 18:32 UTC (permalink / raw)
To: Tilo Schwarz <mail@tilo-schwarz.de>; +Cc: pgsql-sql
Tilo Schwarz <mail@tilo-schwarz.de> writes:
> - 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?
Not easily --- the column info is buried in the pg_trigger.tgargs entries
for the RI triggers, which there is no good way to take apart in plain SQL.
You might care to start experimenting with 7.3 instead; the new
pg_constraint table makes this much easier.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question on SQL and pg_-tables
2002-11-25 16:21 Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-25 18:32 ` Re: Question on SQL and pg_-tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-27 10:13 ` Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-27 20:04 ` Re: Question on SQL and pg_-tables Peter Childs <Blue.Dragon@blueyonder.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Tilo Schwarz @ 2002-11-27 10:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Tilo Schwarz <mail@tilo-schwarz.de> writes:
> > - 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?
>
> Not easily --- the column info is buried in the pg_trigger.tgargs entries
> for the RI triggers, which there is no good way to take apart in plain SQL.
>
> You might care to start experimenting with 7.3 instead; the new
> pg_constraint table makes this much easier.
Thank you, I'll check that out.
Regards,
Tilo
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question on SQL and pg_-tables
2002-11-25 16:21 Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-25 18:32 ` Re: Question on SQL and pg_-tables Tom Lane <tgl@sss.pgh.pa.us>
2002-11-27 10:13 ` Re: Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
@ 2002-11-27 20:04 ` Peter Childs <Blue.Dragon@blueyonder.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Childs @ 2002-11-27 20:04 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 27 November 2002 10:13, Tilo Schwarz wrote:
> > Tilo Schwarz <mail@tilo-schwarz.de> writes:
> > > - 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?
> >
> > Not easily --- the column info is buried in the pg_trigger.tgargs entries
> > for the RI triggers, which there is no good way to take apart in plain
> > SQL.
> >
> > You might care to start experimenting with 7.3 instead; the new
> > pg_constraint table makes this much easier.
>
> Thank you, I'll check that out.
>
> Regards,
>
> Tilo
>
I posted a similar question earlier in the week on General. Since I had no
reply I've come up with this pretty stupid SQL query (its a view so you can
store it in the database....)
create view constraints as
select seven as triggername, eight as constraintname, nine as enabled,
ten as deferrable, eleven as initallydeferred, twelve as relname,
two as localtable, three as foreigntable, four as type, five as localfield,
substring(rest5,1,position('\\000'::bytea in rest5)-1) as foreignfield from (
select seven, eight, nine, ten, eleven, twelve,
one, two, three, four, substring(rest4,1,position('\\000'::bytea in rest4)-1)
as five, substring(rest4,position('\\000'::bytea in rest4)+1) as rest5 from (
select seven, eight, nine, ten, eleven, twelve,
one, two, three, substring(rest3,1,
position('\\000'::bytea in rest3)-1) as four,
substring(rest3,position('\\000'::bytea in rest3)+1) as rest4 from (
select seven, eight, nine, ten, eleven, twelve,
one, two, substring(rest2,1,position('\\000'::bytea in rest2)-1) as three,
substring(rest2,position('\\000'::bytea in rest2)+1) as rest3 from (
select seven, eight, nine, ten, eleven, twelve,
one, substring(rest1,1,position('\\000'::bytea in rest1)-1) as two,
substring(rest1,position('\\000'::bytea in rest1)+1) as rest2 from (
select tgname as seven, tgconstrname as eight, tgenabled as nine,
tgdeferrable as ten, tginitdeferred as eleven, pg_class.relname as twelve ,
substring(tgargs,1,position('\\000'::bytea in tgargs)-1) as one,
substring(tgargs,position('\\000'::bytea in tgargs)+1) as rest1
from pg_trigger, pg_class where tgisconstraint=true and
pg_trigger.tgrelid=pg_class.oid) as a) as b) as c) as e) as f;
Its bad because I could not find a split built in function..
Each constraint has two records I think.... The table also as a count of the
number of arguments in tgargs so if you were to define a split function the
query would be alot simpler!
I've also been looking at upgrading to 7.3 but as I am tring to get it
installed on a separate computer than our main database I need the 7.2 client
tools to still work to access the old database. (running on another computer)
They seam to run fine until you try and run the scripting languages which go
off and find the wrong library (.so files) I managed to get round this by
changing ldconfig to point to the right place but this broke 7.2.....
It would seam that 7.3 and 7.2 are incompatible and even doing a dump and
reload (as the documentation says you should) does not seam to mean that the
sql that worked in 7.2 will work in 7.3.....
I am beginning to hate sql. Its got nothing in it for getting meta data (data
about data) so every body uses non-standard methods. Which means using
standard libraries is a waste of time.....
Perhaps we need a standard set of "views" to tell us the meta data then to
get at the meta data in a different database all you would need to do is
reimplemented the views but this is rather a dirty solution. To something
which is missing in the standard....
Peter Childs
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-11-27 20:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-25 16:21 Question on SQL and pg_-tables Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-25 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-27 10:13 ` Tilo Schwarz <mail@tilo-schwarz.de>
2002-11-27 20:04 ` Peter Childs <Blue.Dragon@blueyonder.co.uk>
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