From: Peter Childs <Blue.Dragon@blueyonder.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Question on SQL and pg_-tables
Date: Wed, 27 Nov 2002 20:04:40 +0000
Message-ID: <200211272004.40327.Blue.Dragon@blueyonder.co.uk> (raw)
In-Reply-To: <200211271113.40827.mail@tilo-schwarz.de>
References: <200211251721.27543.mail@tilo-schwarz.de>
<8774.1038249125@sss.pgh.pa.us>
<200211271113.40827.mail@tilo-schwarz.de>
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
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: Blue.Dragon@blueyonder.co.uk
Subject: Re: Question on SQL and pg_-tables
In-Reply-To: <200211272004.40327.Blue.Dragon@blueyonder.co.uk>
* 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