1

A user wants to invite a friend but I want to do a check first. For example:

SELECT friends_email from invites where friends_email = $1 limit 1;

If that finds one then I want to return a message such as "This friend already invited."

If that does not find one then I want to do an insert

INSERT INTO invites etc...

but then I need to return the primary user's region_id

SELECT region_id from users where user_id = $2

What's the best way to do this?

Thanks.

EDIT --------------------------------------------------------------

After many hours below is what I ended up with in 'plpgsql'.

IF EXISTS (SELECT * FROM invitations WHERE email = friends_email) THEN
  return 'Already Invited';
END IF;

INSERT INTO invitations (email) VALUES (friends_email);

return 'Invited';

I undestand that there are probably dozens of better ways but this worked for me.

user390480
  • 1,655
  • 4
  • 28
  • 61
  • Are you aware of `CASE WHEN ... THEN ... ELSE ... END` in the SQL – Mladen Uzelac Nov 25 '14 at 13:22
  • @MladenUzelac What good would that do for an insert-if-not-exists? – Craig Ringer Nov 25 '14 at 13:34
  • It seems that you will need to write pl/pgsql function for that or solve it in your application. – Mladen Uzelac Nov 25 '14 at 14:15
  • This is a variant of the upsert problem: http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql . If you want to do it with an error trap, use PL/PgSQL `BEGIN ... EXCEPTION` blocks. – Craig Ringer Nov 26 '14 at 01:35
  • Your solution clearly states your intention for the procedure, and unless you face a performance issue, that is the best outcome. If you do wind up with a performance issue later you can then explore other methods, but when that day comes you intention will be very clear and easy to recall when you re-read this. – zxq9 Nov 27 '14 at 01:24

2 Answers2

1

Without writing the exact code snippet for you...

Consider solving this problem by shaping your data to conform to your business rules. If you can only invite someone once, then you should have an "invites" table that reflects this by a UNIQUE rule across whatever columns define a unique invite. If it is just an email address, then declare the "invites.email" as a unique column.

Then do an INSERT. Write the insert so that it takes advantage of Postgres' RETURNING clause to give an answer on success. If the INSERT fails (because you already have that email address -- which was the point of the check you wanted to do), then catch the failure in your application code, and return the appropriate response.

Psuedocode:

Application:

try
    invite.insert(NewGuy)
catch error.UniqueFail
    return "He's already been invited"
# ...do other stuff

Postgres:

INSERT INTO invites
    (data fields + SELECT region thingy)
  VALUES
    (some arrangement of data that includes "region_id")
  RETURNING region_id

If that's hard to make work the first time you try it, phrasing the insert target as a CTE may be helpful. If all else fails, write it procedurally in plpgsql for the time being, making sure the external interface accepts a normal INSERT (so you don't have to change application code later) and sort it out once you know whether or not performance is an issue.

The basic idea here is to let the relational shape of your data obviate the need for any procedural checking wherever you can. That's at the heart of relational data modeling ...somewhat of a lost art these days.

zxq9
  • 13,020
  • 1
  • 43
  • 60
  • zxq9, thank you for your post. I kept coming back to it as I worked through different permutations. I started in regular SQL then ended up in 'plpgsql' and finally ended up with something pretty simple which I added to the original post. Although I was returning the region_id at first I eventually found that I did not need it. But the whole thing was a great learning experience. Thanks again. – user390480 Nov 27 '14 at 01:26
  • @user390480 Great to hear! I'm especially glad you learned a lot in the process. That's the best outcome, ever. – zxq9 Nov 27 '14 at 01:29
-1

You can create SQL stored procedure for implement functionality like described above.

But it is wrong form architecture point of view. See: Direct database manipulation an anti-pattern?

DB have scope of responsibility: store data.

You have to put business logic into your business layer.

Community
  • 1
  • 1
Vitalii Pro
  • 313
  • 2
  • 17