Raising error in postgreSQL

user1686308 picture user1686308 · Sep 21, 2012 · Viewed 48.4k times · Source
CREATE OR REPLACE FUNCTION msgfailerror() RETURNS trigger AS 
' BEGIN 
    IF NEW.noces< new.first_column THEN 
        RAISE EXCEPTION 'cannot have a negative salary'; 
    END IF; 
   return new; 
END' LANGUAGE plpgsql

Trigger

create trigger msgfail before insert on first for each row 
execute procedure msgfailerror()

Giving error:

syntax error at or near "cannot" LINE 5: RAISE EXCEPTION 'cannot have a negative ...

I have almost one validation for each field of row. I want trigger to check all validations while insertion is being done and, raise error log afterwards once for all. Should I use raise exception on raise notice ?

For example:

Insert into first (first_column, noces,dob) values ('4545','75','545') 

I am checking noces is less than first_column, for the same row i want to check if dob > 80 and if first_column is integer and raise error for all validations. Thanks in advance

Answer

Frank Heikens picture Frank Heikens · Sep 21, 2012

The quoting is wrong. It's easier to use dollar quotes $$:

CREATE OR REPLACE FUNCTION msgfailerror() 
RETURNS trigger AS 
$$
BEGIN 
  IF NEW.noces< new.first_column THEN 
    RAISE EXCEPTION 'cannot have a negative salary'; 
  END IF; 
  return new; 
END;
$$
LANGUAGE plpgsql;

But on the other hand, what's wrong with a check constraint?