[SQL] pg_func problem

[Graham Vickrage]

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly