From: Arnau <arnaulist@andromeiberica.com>
To: pgsql-sql@postgresql.org
Subject: help with version checking
Date: Thu, 28 Dec 2006 18:30:55 +0100
Message-ID: <4593FF4F.40706@andromeiberica.com> (raw)
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
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: arnaulist@andromeiberica.com
Subject: Re: help with version checking
In-Reply-To: <4593FF4F.40706@andromeiberica.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