Re: Question on SQL and pg_-tables

On Wednesday 27 November 2002 10:13, Tilo Schwarz wrote:
> > Tilo Schwarz <mail(at)tilo-schwarz(dot)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