Raising error in postgreSQL

Question

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

You are doing numeric comparison but yet you use character literals in your `INSERT` statement. If `noces` and `first_column` are character columns, `<` will not do what you expect. ***Never***, ***ever*** store numbers in character columns! — , Sep 21 '12 at 11:33

score 42 · Accepted Answer · edited Jan 13 '23 at 16:51

42

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?

edited Jan 13 '23 at 16:51

Marc Bannout

388
4
15

answered Sep 21 '12 at 10:58

Frank Heikens

117,544
24
142
135

using above code it is not showing any error .. but also not raising any exception just inserting value.. and will check constraint show any error ? I do not want to abort action i just want to throw a message like values entered by you are failing these these constraints . – user1686308 Sep 21 '12 at 11:16
3

another +1 for check constraints – Sep 21 '12 at 11:30
@user1686308: If you throw an exception - like you do in your question - you always roll back (abort) the transaction - unless you catch the exception. A violated CHECK constraint would do the same. You need to raise a *warning* or *notice* in this case. – Erwin Brandstetter Sep 21 '12 at 11:30
@user1686308: See this SQLFiddle for an example: http://sqlfiddle.com/#!1/82c39/1 – Sep 21 '12 at 11:34
Thank you I will use check constraints wherever possible, I have to work on around 180 validations . wish me luck.Thanks all for help – user1686308 Sep 21 '12 at 11:44

Mehdi Sadighian · Answer 2 · 2017-05-23T09:05:01.913

5

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

edited May 23 '17 at 09:05

answered May 23 '17 at 09:02

Mehdi Sadighian

108
1
8

Welcome to Stack Overflow! Please don't leave your email address under your post; contact details belong to your profile. – Glorfindel May 23 '17 at 09:04

score 1 · Answer 3 · edited Mar 02 '16 at 06:32

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;

Raising error in postgreSQL

3 Answers3

Linked