Date: Wed, 29 Oct 2003 12:57:52 +0200
From: Stef <svb@ucs.co.za>
To: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
Subject: Table versions
Message-Id: <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_12_57_52_+0200_3=FmPbV_FA4nzzJ+"

--Signature=_Wed__29_Oct_2003_12_57_52_+0200_3=FmPbV_FA4nzzJ+
Content-Type: multipart/mixed;
 boundary="Multipart=_Wed__29_Oct_2003_12_57_52_+0200_CMNYie2j7gRT6dpG"

--Multipart=_Wed__29_Oct_2003_12_57_52_+0200_CMNYie2j7gRT6dpG
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

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

--Multipart=_Wed__29_Oct_2003_12_57_52_+0200_CMNYie2j7gRT6dpG
Content-Type: application/octet-stream;
 name="test.plpgsql"
Content-Disposition: attachment;
 filename="test.plpgsql"
Content-Transfer-Encoding: base64

Q1JFQVRFIG9yIHJlcGxhY2UgRlVOQ1RJT04gaW5jcmVtZW50X3RhYmxlX3Zl
cnNpb24gKCkgUkVUVVJOUyBPUEFRVUUgQVMgJwogICAgREVDTEFSRSAKICAg
ICAgIHZfbmV3X3ZlcnNpb24gaW50NDsKICAgIEJFR0lOCiAgICAKICAgICAg
IHNlbGVjdCBpbnQ0KGRlc2NyaXB0aW9uKSBpbnRvIHZfbmV3X3ZlcnNpb24g
ZnJvbSBwZ19kZXNjcmlwdGlvbiAKICAgICAgIHdoZXJlIG9iam9pZCA9IE5F
Vy5hdHRyZWxpZDsKICAgICAgIAogICAgICAgdXBkYXRlIHBnX2Rlc2NyaXB0
aW9uIHNldCBkZXNjcmlwdGlvbiA9IHZfbmV3X3ZlcnNpb24gd2hlcmUgb2Jq
b2lkID0gTkVXLmF0dHJlbGlkOwoKICAgICAgIHJldHVybiBORVc7CiAgICBF
TkQ7CicgTEFOR1VBR0UgJ3BscGdzcWwnOwo=

--Multipart=_Wed__29_Oct_2003_12_57_52_+0200_CMNYie2j7gRT6dpG
Content-Type: application/octet-stream;
 name="test.trigger"
Content-Disposition: attachment;
 filename="test.trigger"
Content-Transfer-Encoding: base64

Q1JFQVRFIFRSSUdHRVIgdGFibGVfdmVyc2lvbl9pbmNyZW1lbnQgQkVGT1JF
IElOU0VSVCBPUiBVUERBVEUgb24gcGdfYXR0cmlidXRlCkZPUiBFQUNIIFJP
VyBFWEVDVVRFIFBST0NFRFVSRSBpbmNyZW1lbnRfdGFibGVfdmVyc2lvbigp
Owo=

--Multipart=_Wed__29_Oct_2003_12_57_52_+0200_CMNYie2j7gRT6dpG--

--Signature=_Wed__29_Oct_2003_12_57_52_+0200_3=FmPbV_FA4nzzJ+
Content-Type: application/pgp-signature

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

iD8DBQE/n501gDYs3UkRjZkRAgpFAJ0eUF7z51Cy6pof7HavuB4wzbUgDwCgiBua
H2XNIKSxXTOtYWBbTs1pzbw=
=2YjD
-----END PGP SIGNATURE-----

--Signature=_Wed__29_Oct_2003_12_57_52_+0200_3=FmPbV_FA4nzzJ+--