I'd probably make a small change to make this a little cleaner.
Specifically, change check_version() to take an argument, which is the
needed version, and check this against the current value in
agenda_version, throwing the exception if they don't match. Once you've
written this, you'll never need to touch it again (no more DROP
FUNCTIONs required).
Then, at the end of your update script, you update the version in the
table via normal SQL (no need for a single-use function that does this).
With these tweaks, your update scripts could be simpler, like this:
BEGIN;
SELECT check_version('1.0.0.0');
-- Do all your updates etc. here --
UPDATE agenda_version SET version = '1.0.0.1' WHERE id =1;
COMMIT;
HTH. Good luck...
-chris
Arnau wrote:
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;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
