Date: Thu, 28 Dec 2006 12:45:02 -0500
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: help with version checking
In-reply-to: <4593FF4F.40706@andromeiberica.com>
To: arnaulist@andromeiberica.com, pgsql-sql@postgresql.org
Message-id: <000001c72aa7$e705f1e0$0b00a8c0@tedy>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
Thread-index: Accqphd3Irj5eeXwROKBdcKnYH1O9QAARGQg

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

You should define a PL/PGSQL function such as:

CREATE OR REPLACE FUNCTION check_version()
  RETURNS void
AS $$
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;
$$ LANGUAGE PLPGSQL;


Regards,

--
Daniel