From: Arnau <arnaulist@andromeiberica.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with version checking
Date: Fri, 29 Dec 2006 15:08:37 +0100
Message-ID: <45952165.6080403@andromeiberica.com> (raw)
In-Reply-To: <45940DD3.3040509@earthcomber.com>
References: <000001c72aa7$e705f1e0$0b00a8c0@tedy>
<45940403.4010909@andromeiberica.com>
<21284.1167328587@sss.pgh.pa.us>
<459409A2.9020103@andromeiberica.com>
<45940DD3.3040509@earthcomber.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: arnaulist@andromeiberica.com
Subject: Re: help with version checking
In-Reply-To: <45952165.6080403@andromeiberica.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox