Message-ID: <4593FF4F.40706@andromeiberica.com>
Date: Thu, 28 Dec 2006 18:30:55 +0100
From: Arnau <arnaulist@andromeiberica.com>
Reply-To: arnaulist@andromeiberica.com
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: help with version checking
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all!,

   I've got the following problem and I don't know how to solve it in 
PostgreSQL.

   I'd like to add a version checking to my db scripts. That is, I have 
the db creation scripts and the changes/upgrade script, and there is a 
table inside each db that holds the version of script executed. So 
before apply the upgrade script I'd like to check if the installed 
version matches the expected if not then show an error and terminate the 
script execution.

   In Oracle I used to do:

DEFINE PREVIOUS_VERSION = '2.3.5.10'

DECLARE
         v_version varchar2(100);
         v_ok number;
BEGIN
         select version into v_version from version where id = 1;
         if v_version <> '&PREVIOUS_VERSION' then
           RAISE_application_error(-20000,
             'This script needs SMC version [' ||
             '&PREVIOUS_VERSION' || '] detected version is [' ||
             v_version || ']' );
         end if;
END;
/


I tried to do the following in PostgreSQL:

DECLARE
   v_version VARCHAR;

BEGIN
   SELECT version INTO v_version FROM version WHERE id = 1;

   IF v_version <> ''1.0.0.0'' THEN
     RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, 
detected version %'', v_version;
   END IF;

END;

//The upgrade stuff

but when I execute it, gives a lot of errors:

psql -d dermagier -f upgrade_agenda.sql
psql:upgrade_agenda.sql:2: ERROR:  syntax error at or near "VARCHAR" at 
character 21
psql:upgrade_agenda.sql:5: ERROR:  syntax error at or near "SELECT" at 
character 9
psql:upgrade_agenda.sql:8: ERROR:  syntax error at or near "IF" at 
character 3
psql:upgrade_agenda.sql:9: ERROR:  syntax error at or near "IF" at 
character 7
psql:upgrade_agenda.sql:11: WARNING:  there is no transaction in progress
COMMIT


Anybody knows how I can do this or which is the best way to do it?

Thank you very much
-- 
Arnau