From: Chris Dunworth <cdunworth@earthcomber.com>
To: arnaulist@andromeiberica.com
Cc: pgsql-sql@postgresql.org
Subject: Re: help with version checking
Date: Fri, 29 Dec 2006 11:17:37 -0600
Message-ID: <45954DB1.7080208@earthcomber.com> (raw)
In-Reply-To: <45952165.6080403@andromeiberica.com>
References: <000001c72aa7$e705f1e0$0b00a8c0@tedy>
<45940403.4010909@andromeiberica.com>
<21284.1167328587@sss.pgh.pa.us>
<459409A2.9020103@andromeiberica.com>
<45940DD3.3040509@earthcomber.com>
<45952165.6080403@andromeiberica.com>
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;
>
>
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: cdunworth@earthcomber.com, arnaulist@andromeiberica.com
Subject: Re: help with version checking
In-Reply-To: <45954DB1.7080208@earthcomber.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