From: Graham Vickrage <graham@gpmd.co.uk>
To: pgsql-sql@postgresql.org
Subject: pg_func problem
Date: Thu, 20 Feb 2003 12:58:34 -0000
Message-ID: <007b01c2d8df$c9cdf5a0$6d00000a@GVICKRAGE> (raw)
Hi All,
I am trying to use the function below, it works fine on my dev server
running 7.2.3 but does not work on my production server running 7.1.2.
(both on linux)
Would anyone be able to shed some light on why this is the case.
The error I get is:
ERROR during compile of 'change_sup_ord_status' near line 19
parse error at or near "IF"
I am running the following query against the function below: -
Query: UPDATE supplier_order SET status = 'Pending' where id = 2003;
CREATE FUNCTION change_sup_ord_status () RETURNS OPAQUE AS '
DECLARE
num INT4;
BEGIN
IF OLD.status = ''Complete'' AND NEW.status != ''Complete'' THEN
--Invalid option
RAISE EXCEPTION ''This is an invlid status change '';
ELSIF OLD.status = ''Pending'' THEN
IF NEW.status = ''Complete'' THEN
UPDATE supplier_order_detail SET
status=''Complete'' WHERE supplier_order_id = OLD.id AND
status=''Pending'';
ELSIF NEW.status = ''VOID'' OR NEW.status = ''Saved''
THEN
SELECT count(*) INTO num FROM
supplier_order_detail WHERE supplier_order_id = OLD.id AND status =
''Complete'';
IF num > 0 THEN
RAISE EXCEPTION ''Invalid change of
status, some of the order has already been entered into stock'';
END IF;
END IF;
ELSIF (OLD.status = ''VOID'') AND NEW.status = ''Complete'' THEN
RAISE EXCEPTION ''Invalid change of status'';
END IF;
RETURN NEW;
END;' LANGUAGE 'plpgsql';
Thanks in advance.
Graham
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: graham@gpmd.co.uk
Subject: Re: pg_func problem
In-Reply-To: <007b01c2d8df$c9cdf5a0$6d00000a@GVICKRAGE>
* 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