Обсуждение: Question on SQL and pg_-tables

Поиск
Список
Период
Сортировка

Question on SQL and pg_-tables

От
Tilo Schwarz
Дата:
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;


Re: Question on SQL and pg_-tables

От
Tom Lane
Дата:
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


Re: Question on SQL and pg_-tables

От
Tilo Schwarz
Дата:
> 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


Re: Question on SQL and pg_-tables

От
Peter Childs
Дата:
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
intgargs)-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
andeven 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