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
