Re: help with version checking

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