We have a perl script that is run by various development, QA and production release folks from client servers to upgrade (change) schemas on a database server.
I had a need to modify the script to handle the pg_class.reltriggers column rename to relhastriggers in PG version 8.4.
To accomplish this, I had to test the database version.
This is easy enough, just use the DBD::Pg function:
my $pgver = $dbh->{'pg_server_version'};
Which returns '80401' for version 8.4.1.
If the $pgver ge 80400, then use the new column name, no problemo.
Additionally, we have had other issues with the client pg version being too old to handle certain newer features in the functions, for example double-dollar $$ quoting, or just major versions in psql which is often used from the application servers.
So I decided to enhance the script to check the client version as well and compare to the server version, then possibly give the user a warning.
However, I didn't want to display the 'raw' version strings ala "hey, your client is version 80304 and your database version is 80401 -- could be some ungoodness happnin'".
Besides, the last number (patch version) doesn't make a difference - just the first two numbers, like 8.3 different than 8.4, because 8.4.0 and 8.4.1 will work just fine together
So I went to work on the beautifying the version numbers for those non-DBA folks that use the script to understand what this potential warning meant.
For this code example, let's say that the version for the client is 8.3.3 and for the database is 8.4.1.
First get the 'raw' versions:
my $pgver_srv = $dbh->{'pg_server_version'}; # returns '80401'
my $pgver_lib = $dbh->{'pg_lib_version'}; #returns the client library version '80303'
Then drop the last 2 digits (the values after the second decimal):
my $pgver_srv_maj = sprintf '%.3g',($pgver_srv /10000); #returns '8.04'
my $pgver_lib_maj = sprintf '%.3g',($pgver_lib /10000); #returns '8.03'
Then remove the meaningless leading zero after the decimal:
$pgver_srv_maj =~ s/\.0*/\./; #returns '8.4'
$pgver_lib_maj =~ s/\.0*/\./; #returns '8.3'
Now these variables can be compared and displayed to the user if necessary.
