在PostgreSQL中引发错误 [英] 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

触发

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

提供错误:

无法"显示或附近的语法错误LINE 5:RAISE EXCEPTION'不能为负...

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 ?

例如:

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

我正在检查noces小于first_column，对于同一行，我想检查dob> 80以及first_column是否为整数并为所有验证引发错误.预先感谢

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

推荐答案

引号错误.使用美元报价$$ $$更容易:

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?

这篇关于在PostgreSQL中引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！