-1

I am trying to check if an email address already exists, and add 1 if it does (and 1 if even the email+1 exists and so on). But so far I can't even figure out how to check if it exists, inside a procedure.

if exists (select 1 from table where email='something') then ...

Gives back an error ("function or pseudo-column 'EXISTS' may be used inside a SQL statement only)". Tried other stuff as well, but those might not be worth mentioning.

After I have this I plan on making a while loop for adding 1 as much as needed.

Saeq
  • 249
  • 2
  • 11
  • I may not have been very specific, just add 1 to the end of the email, for example bunny -> bunny1. – Saeq May 09 '15 at 18:38

1 Answers1

1

You can select the number of matching records into a variable (which you have declared), and then check that variable's value:

select count(*) into l_count
from my_table
where email = 'something';
if l_count > 0 then
  -- record exists
  ...
else
  -- record does not exist
  ...
end if;

select ... into always has to get exactly one record back, and using the count aggregate function means that happens, evenif more than one matching record exists.

That hopefully covers your specific issue about checking for existance. As for your underlying goal, it sounds like you're trying to find an unused value by incrementing a suffix. If so, this similar question might help. That is looking for usernames rather than emails,but the principle is the same.

As pointed out in comments, simultaneous calls to your procedure might still try to use the same suffix value; so you still need a unique constraint, and handling for that being violated in that scenario. I think that's beyond what you asked about here though.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • @Saeq - glad it helped; check my edit to see if the linked question helps your overall goal. – Alex Poole May 09 '15 at 18:38
  • 1
    It could be a little bit optimized by adding `rownum = 1` condition. – Nick Krasnov May 09 '15 at 18:40
  • True, though I assume duplicates aren't really expected in this case; I only changed `= 1` to `> 0` at the last minute just in case *8-) – Alex Poole May 09 '15 at 18:47
  • 1
    Given the use case explained by the OP in a comment to his question, how does that behave if you have two concurrent inserts of the same email ? Can't we end up inserting twice the email with the _same_ suffix ? – Sylvain Leroux May 09 '15 at 18:47
  • Also true, but like, I said, I was adressing the specific existance issue. The overall process would still need a unique check and handling for that being violated by simultaneous calls. – Alex Poole May 09 '15 at 18:50
  • About inserting the email wtih the same suffix twice, I check for the "email+1" again if it already exists. I have the code up already, it seems to work after tests. – Saeq May 09 '15 at 19:02
  • @Saeq - it's not just if it exists at the moment you check; Sylvain's very valid point is that if two people call the proc *at the same time*, both will see that email+1 doesn't exist, and both try to use it. You'll either get a duplicate inserted, or a unique constraint violation. – Alex Poole May 09 '15 at 19:50
  • @Saeq - you could do [something like this](http://stackoverflow.com/a/16269854/266304) to catch the duplicate and try another incremented suffix, until you find one that is unique (or that you get to first). You could still do what you're doing now to try to avoid the duplicate in the single-user case, but catching the exception and retrying will help in the multi-user scenario. – Alex Poole May 09 '15 at 19:59