CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Antonio
Antonio

Reputation: 602

CASE-WHEN within function POSTGRESQL

I have a function to insert some values into a table, but before inserting I want to check if e-mail address it's right. If not, break the function and returns an error. Case true, go on.

case when _email ~ '^[^@\s]+@[^@\s]+(\.[^@\s]+)+$' = true 
then raise exception 'Incorrect email'

_email is the parameter of funcion. But it's not working. Should I use "IF" or other conditional?

Upvotes: 0

Views: 114

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659297

CASE works, but IF seems more appropriate.
You have some pointless noise in the expression and I think you got the logic backwards: 'Incorrect email' should be triggered if _email does not match the pattern:

IF _email ~ '^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'  -- drop the pointless "= true"
THEN  -- do nothing - I inverted the logic
ELSE RAISE EXCEPTION 'Incorrect email';
END IF;

The new ASSERT (Postgres 9.5+) would also work, but that's really meant for debugging:

ASSERT _email ~ '^[^@\s]+@[^@\s]+(\.[^@\s]+)+$', 'Incorrect email';

Upvotes: 2

cableload
cableload

Reputation: 4385

You should be able to use the case inside plpgsql. Obviously what you are trying to do can be done through if statement as well...

 case when _email ~ '^[^@\s]+@[^@\s]+(\.[^@\s]+)+$' = true then
           raise exception 'Incorrect email';
      else
           --proceed with insert
  end case;

Upvotes: 0

Related Questions