Guest User

Untitled

a guest
Apr 7th, 2015
205
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.78 KB | None | 0 0
  1. CREATE TABLE users (
  2. id serial primary_key
  3. foo int null,
  4. bar int default 9001,
  5. baz int null
  6. );
  7.  
  8. CREATE FUNCTION upsert() RETURNS TRIGGER AS
  9. $$
  10. BEGIN
  11. LOOP
  12. -- first try to update the key
  13. UPDATE users
  14. SET name = NEW.name,
  15. foo = NEW.foo,
  16. bar = NEW.bar,
  17. baz = NEW.baz,
  18. modified_at = NOW()
  19. WHERE id = NEW.id;
  20.  
  21. IF found THEN
  22. RETURN NULL;
  23. END IF;
  24.  
  25. RETURN NEW;
  26. END LOOP;
  27. END;
  28. $$
  29. LANGUAGE plpgsql;
  30.  
  31. CREATE TRIGGER upsert_trigger BEFORE INSERT ON users
  32. FOR EACH ROW EXECUTE upsert();
  33.  
  34. INSERT INTO users(id, foo, bar, baz) VALUES (1, 2, 3, 4);
  35. INSERT INTO users(id, foo) VALUES (1, 10);
  36.  
  37. id: 1,
  38. foo: 10,
  39. bar: 9001,
  40. baz: null
  41.  
  42. id: 1,
  43. foo: 10,
  44. bar: 3,
  45. baz: 4
Advertisement
Add Comment
Please, Sign In to add comment