Thread: Trigger Compile Error

Hello,

I have 2 identical postgresql databases (both 7.0.3) on 2 different server
(1 on Debian, 1 on Mandrake).

I have the same insert trigger and function on both databases.  On the first
server the trigger works as intended, on the 2nd it fails and gives me a the
following error:

NOTICE:  plpgsql:  ERROR during compile of trigger_insert_customer near line
1
"RROR:  parse error at or near "


Here is the SQL statements that were run (on both databases) to create the
trigger and corresponding function:

--
-- Function and trigger to enforce uniqueness of customer email
--
create function trigger_insert_customer()
RETURNS opaque
AS 'DECLARE
   matches record;

   BEGIN
      IF new.cust_email IS NOT null THEN
         IF (length(new.cust_email) > 0) THEN
            SELECT INTO matches COUNT(*) FROM customer
            WHERE customer.business_sakey = new.business_sakey AND
customer.cust_email = new.cust_email;

            IF matches.count > 0 THEN
               RAISE EXCEPTION ''Customer Email Already Exists for this
business.  Email must be unique'';
            END IF;
         END IF;
      END IF;
      RETURN new;
   END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_insert_customer BEFORE INSERT ON customer
FOR EACH ROW
EXECUTE PROCEDURE trigger_insert_customer();


Again, it works on 1 database, but not the other.  Both databases were
created from the same scripts, both have plpgsql created.

The trigger is to enforce uniqueness of email address, if one is present
(duplicate nulls and empty strings are allowable).

Any idea why I might be getting the parse error?

Thanks for the help!

Mark

--
Mark G. Gilmore
Software Development
TheImageGroup
mgilmore@imagegroup.com

"Mark Gilmore" <mgilmore@pobox.com> writes:
> NOTICE:  plpgsql:  ERROR during compile of trigger_insert_customer near line
> 1
> "RROR:  parse error at or near "

Change your CR/LF newlines to plain LF.

            regards, tom lane