Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE users (
- id serial primary_key
- foo int null,
- bar int default 9001,
- baz int null
- );
- CREATE FUNCTION upsert() RETURNS TRIGGER AS
- $$
- BEGIN
- LOOP
- -- first try to update the key
- UPDATE users
- SET name = NEW.name,
- foo = NEW.foo,
- bar = NEW.bar,
- baz = NEW.baz,
- modified_at = NOW()
- WHERE id = NEW.id;
- IF found THEN
- RETURN NULL;
- END IF;
- RETURN NEW;
- END LOOP;
- END;
- $$
- LANGUAGE plpgsql;
- CREATE TRIGGER upsert_trigger BEFORE INSERT ON users
- FOR EACH ROW EXECUTE upsert();
- INSERT INTO users(id, foo, bar, baz) VALUES (1, 2, 3, 4);
- INSERT INTO users(id, foo) VALUES (1, 10);
- id: 1,
- foo: 10,
- bar: 9001,
- baz: null
- id: 1,
- foo: 10,
- bar: 3,
- baz: 4
Advertisement
Add Comment
Please, Sign In to add comment