CASE-WHEN within function POSTGRESQL

Question

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?

Erwin Brandstetter · Accepted Answer · 2016-05-24 05:12:46Z

2

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';

answered May 24, 2016 at 5:12

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antonio Over a year ago

Worked great. I'm using this way in my function. Thanks!

cableload · Accepted Answer · 2016-05-24 02:12:36Z

0

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;

answered May 24, 2016 at 2:12

cableload

4,4156 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

CASE-WHEN within function POSTGRESQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related