Date: Wed, 29 Oct 2003 13:21:33 +0200
From: Stef <svb@ucs.co.za>
To: Stef <svb@ucs.co.za>
Cc: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [SQL] Table versions
Message-Id: <20031029132133.308ccd0e.svb@ucs.co.za>
In-Reply-To: <20031029125752.35069e3b.svb@ucs.co.za>
References: <20031029125752.35069e3b.svb@ucs.co.za>
User-Agent: sillypheed-claws (anti-aliased)
Mime-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg="pgp-sha1";
	boundary="Signature=_Wed__29_Oct_2003_13_21_33_+0200_coy52aO_Fwv=hs4F"

--Signature=_Wed__29_Oct_2003_13_21_33_+0200_coy52aO_Fwv=hs4F
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Correction on the function :
The function currently on the database did has
       select int4(description) + 1 into v_new_version from pg_description 
       where objoid = NEW.attrelid;

in stead of 
       select int4(description) into v_new_version from pg_description 
       where objoid = NEW.attrelid;

##START##
=> Hi all,
=> 
=> I'm trying to create some kind of table version control
=> system for approximately 300 postgres databases 
=> ranging in version from 7.1.2 to 7.3.4.
=> 
=> I compared the "pg_dump -s" output between 
=> the various versions of databases, but the format is inconsistent,
=> and I can't do diff's to check that table structures are identical
=> on the various databases this way. 
=> 
=> What I did next, is put a trigger on pg_attribute that should, in theory,
=> on insert and update, fire up a function that will increment a version
=> number on a table comment every time a table's structure is modified.
=> I tried to make the function update a comment on pg_description to
=> accomplish this. 
=> 
=> I'm having a lot of trouble doing this and testing it, and after plenty tries
=> it's still not working. I've attached the trigger statement and the plpgsql function.
=> (There might be a few mistakes, and I haven't attempted to cater for
=> system columns and  multiple changes yet.)
=> 
=> Can somebody please tell me if what I'm trying will ever work, or
=> maybe an alternative (easier) way to compare a specific table's 
=> structure amongst various databases, that are not necessarily 
=> on the same network, nor of the same version of postgres.  
=>  
=> Regards 
=> Stefan
=> 

--Signature=_Wed__29_Oct_2003_13_21_33_+0200_coy52aO_Fwv=hs4F
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/n6LJgDYs3UkRjZkRAq63AKCuzvXmGvl2csOpAjeuPTLbE+RSZgCeMz7P
D1YFH4VvueMlInA8hWN8Ypc=
=dNk5
-----END PGP SIGNATURE-----

--Signature=_Wed__29_Oct_2003_13_21_33_+0200_coy52aO_Fwv=hs4F--