Message-ID: <45952165.6080403@andromeiberica.com>
Date: Fri, 29 Dec 2006 15:08:37 +0100
From: Arnau <arnaulist@andromeiberica.com>
Reply-To: arnaulist@andromeiberica.com
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
CC: pgsql-sql@postgresql.org
Subject: Re: help with version checking
References: <000001c72aa7$e705f1e0$0b00a8c0@tedy>
	<45940403.4010909@andromeiberica.com>
	<21284.1167328587@sss.pgh.pa.us>
	<459409A2.9020103@andromeiberica.com>
	<45940DD3.3040509@earthcomber.com>
In-Reply-To: <45940DD3.3040509@earthcomber.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

   Thanks for all replies, taking into account all your suggestions and 
my google research I arrived to the next script. I'd like to know your 
opinion. Hopefully this will be useful for somebody else.


--------------------------------

--used to stop the script execution on any error
\set ON_ERROR_STOP 1

--disable the autocommit
\set AUTOCOMMIT off

BEGIN;

   /*
     Helper function used to check the current version. If it isn't
     the expected then raise an error an abort the installation.
   */
   CREATE OR REPLACE FUNCTION check_version() RETURNS void AS '
     DECLARE
       current_version VARCHAR;
       needed_version VARCHAR;

     BEGIN
       --define the expected version
       needed_version := ''1.0.0.0'';

       SELECT version INTO current_version FROM agenda_version WHERE id = 1;

       IF current_version <> needed_version THEN
         RAISE EXCEPTION ''This script needs Agenda version %, detected 
version %'', needed_version, current_version;
         RETURN;
       END IF;

       RETURN;

     END;
   ' LANGUAGE 'plpgsql';


   /*
     Helper function used update the version to the current version.
   */
   CREATE OR REPLACE FUNCTION update_version() RETURNS void AS'
     DECLARE
       current_version VARCHAR;

     BEGIN
       current_version := ''1.0.0.1'';

       UPDATE agenda_version set version = current_version where id = 1;

       RETURN;
     END;
   ' LANGUAGE 'plpgsql';


   /*
     The first action ALWAYS MUST BE SELECT check_version() to ensure
     that the current version is the one needed for this changes script.
   */
   SELECT check_version();


   /*
     All the actions that must be performed by the changes script
   */


   /*
     The last actions ALWAYS MUST BE:
       SELECT update_version();
       DROP FUNCTION check_version();
       DROP FUNCTION update_version();

     to update the script version and remove the helper functions
   */
   SELECT update_version();
   DROP FUNCTION check_version();
   DROP FUNCTION update_version();


--close the transaction
END;


-- 
Arnau