Thread: Question on SQL and pg_-tables
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;
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
> 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
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