Re: help with version checking - Mailing list pgsql-sql

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;
>
>