在 postgreSQL 中引发错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12529069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Raising error in postgreSQL
提问by user1686308
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 ...
“不能”第 5 行或附近的语法错误：RAISE EXCEPTION '不能有负数 ...
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 ?
我对行的每个字段几乎都有一个验证。我希望触发器在插入完成时检查所有验证，然后一劳永逸地引发错误日志。我应该在 raise 通知上使用 raise exception 吗？
For example:
例如：
Insert into first (first_column, noces,dob) values ('4545','75','545')
I am checking nocesis 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
我正在检查noces小于first_column，对于同一行，我想检查 dob > 80 以及 first_column 是否为整数并为所有验证引发错误。提前致谢
回答by Frank Heikens
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?
但另一方面，检查约束有什么问题？
回答by Loek Bergman
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.
我同意 Frank 的观点，你可以更好地使用约束，但你称之为验证。验证通常在插入之前完成。如果您想验证插入，您可以使用函数而不是触发器或约束。
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;
回答by Mehdi Sadighian
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
'' = 两个单引号
