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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly