Raising error in postgreSQL

问题:

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

回答1:

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?

回答2:

I agree with Frank that you could better use constraints, but you call it validation. Validation is typically done before insertion takes place. If you would like to validate insertions, you could use functions instead of triggers or constraints.

When you would write functions is the answer to your question to raise exceptions or notices that as long as there has been no write action a notice would suffice (together with leaving the function). Once there has been a write to the database, do you have to use exceptions as they perform a rollback.

Like this:

CREATE OR REPLACE FUNCTION field_validate(p_int int) RETURNS boolean AS $$

DECLARE
 i_id int;
BEGIN 
  if p_int > 10 then
   raise notice 'should be smaller then 10';
   return false;
  end if;
  insert into tbl_numbers(firstfield) values(p_int) returning id in i_id;
  insert into tbl_fake(nofield) values(i_id);
  return true;
EXCEPTION
  WHEN raise exception THEN
   return false;
END;
$$ LANGUAGE plpgsql;

回答3:

there is nothing wrong with you the only thing is using quotes

please change :

RAISE EXCEPTION 'cannot have a negative salary';

to:

RAISE EXCEPTION ''cannot have a negative salary'';

'' is different from "

'' = two single quotes