Message-ID: <45940B07.4010708@andromeiberica.com>
Date: Thu, 28 Dec 2006 19:20: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
CC: pgsql-sql@postgresql.org
Subject: Re: help with version checking
References: <000001c72aa7$e705f1e0$0b00a8c0@tedy>
	<45940403.4010909@andromeiberica.com>
	<21284.1167328587@sss.pgh.pa.us>
	<459409A2.9020103@andromeiberica.com>
In-Reply-To: <459409A2.9020103@andromeiberica.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Arnau wrote:
> Tom Lane wrote:
>> Arnau <arnaulist@andromeiberica.com> writes:
>>>    I don't want, if it's possible, to create a function.
>>
>> Unlike Oracle, PG makes a strong distinction between SQL and
>> programmable languages (including plpgsql).  You can't write
>> plpgsql code without putting it into a function.
>>
>>             regards, tom lane
>>
> 
> I've tried Daniel's suggestion but the Raise doesn't terminate the 
> script execution, so if doesn't do what I need. Notice the Update 1
> 
> arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql
> CREATE FUNCTION
> psql:upgrade_agenda.sql:16: ERROR:  This script needs Agenda version 
> 1.0.0.0, detected version 1.0.0.1
> UPDATE 1
> 
> 

I paste the script I have created:

CREATE OR REPLACE FUNCTION check_version() RETURNS void
AS '
DECLARE
   v_version VARCHAR;

BEGIN
   SELECT version INTO v_version FROM agenda_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';

SELECT check_version();

UPDATE agenda_version set version = '1.0.0.1' where id = 1;


-- 
Arnau