[SQL] help with version checking

[Arnau]

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq