wizpig64 · August 29, 2015 14:18 · Mar 31, 2015 · Mar 31, 2015
diff --git a/one_to_one.sql b/one_to_one.sql
@@ -4,6 +4,11 @@
 -- specifies a non-null value. I needed this for my project (MS-Access was throwing a fit), but it may not be the
 -- default behavior for serial primary keys.
 
+-- todo for UPDATE and DELETE: it's possible that a foo exists without a bar relating to it. currently, UPDATE and
+-- DELETE could be used to mess with rows on foo that shouldn't be messed with (for example they could be rows with
+-- different one-to-one relationships). This won't matter for most applications, but it allows for some potentially
+-- malicious actions that should be caught.
+
 -- drop view foobar;
 -- drop table bar;
 -- drop table foo;

diff --git a/one_to_one.sql b/one_to_one.sql
@@ -0,0 +1,117 @@
+-- This is an implementation of a one-to-one relationship between two tables in PostgreSQL, as well as a view that
+-- presents the two as a single table, accepting INSERTs, UPDATEs and DELETEs just like a flat table would.
+-- One thing that may or may not be noteworthy is how foo.id's sequencer is manually incremented even when the user
+-- specifies a non-null value. I needed this for my project (MS-Access was throwing a fit), but it may not be the
+-- default behavior for serial primary keys.
+
+-- drop view foobar;
+-- drop table bar;
+-- drop table foo;
+
+CREATE TABLE foo (
+  id serial PRIMARY KEY,
+  a integer,
+  b integer
+);
+
+CREATE TABLE bar (
+  foo_id integer PRIMARY KEY REFERENCES foo ON DELETE CASCADE ON UPDATE CASCADE,
+  c integer,
+  d integer
+);
+
+CREATE VIEW foobar AS
+  SELECT
+    foo.id,
+    foo.a,
+    foo.b,
+    bar.c,
+    bar.d
+  FROM foo, bar
+  WHERE foo.id = bar.foo_id
+  ORDER BY foo.id
+;
+
+CREATE OR REPLACE FUNCTION foobar_update() RETURNS TRIGGER AS $$
+  DECLARE
+    new_id integer;
+  BEGIN
+    -- Perform the required operation on foo and bar
+    -- to reflect the change made to foobar.
+    IF (TG_OP = 'DELETE') THEN
+      DELETE FROM foo WHERE id = OLD.id;
+      -- no need to delete from bar, that should be cascaded.
+      IF NOT FOUND THEN RETURN NULL; END IF;
+      RETURN OLD;
+    ELSIF (TG_OP = 'UPDATE') THEN
+      UPDATE foo SET id = NEW.id,
+                      a = NEW.a,
+                      b = NEW.b WHERE id = OLD.id;
+      IF NOT FOUND THEN RETURN NULL; END IF;
+      UPDATE bar SET c = NEW.c,
+                     d = NEW.d WHERE foo_id = NEW.id;
+                     -- use NEW.id here because changes should be cascaded already.
+      IF NOT FOUND THEN RETURN NULL; END IF;
+      RETURN NEW;
+    ELSIF (TG_OP = 'INSERT') THEN
+      -- pk should be what is given by the user, or whatever's next in the sequence.
+      -- evaluating nextval() into a variable makes sure it's incremented always.
+      new_id := nextval('foo_id_seq');
+      NEW.id := COALESCE(NEW.id, new_id);
+      INSERT INTO foo (id,
+                       a,
+                       b)
+               VALUES (NEW.id,
+                       NEW.a,
+                       NEW.b);
+      INSERT INTO bar (foo_id,
+                       c,
+                       d)
+               VALUES (NEW.id,
+                       NEW.c,
+                       NEW.d);
+      RETURN NEW;
+    END IF;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER foobar_update_trigger
+INSTEAD OF INSERT OR UPDATE OR DELETE ON foobar
+  FOR EACH ROW EXECUTE PROCEDURE foobar_update();
+
+
+-- now for some testing
+
+INSERT INTO foobar (a, b, c, d) VALUES (1, 2, 3, 4);
+INSERT INTO foobar (a, b, c, d) VALUES (2, 3, 4, 5);
+INSERT INTO foobar (a, b, c, d) VALUES (3, 4, 5, 6);
+INSERT INTO foobar (a, b, c, d) VALUES (4, 5, 6, 7);
+INSERT INTO foobar (a, b, c, d) VALUES (5, 6, 7, 8);
+INSERT INTO foobar (a, b, c, d) VALUES (6, 7, 8, 9);
+INSERT INTO foobar (a, b, c, d) VALUES (1, default, default, default);
+INSERT INTO foobar (a, b, c, d) VALUES (default, 1, default, default);
+INSERT INTO foobar (a, b, c, d) VALUES (default, default, 1, default);
+INSERT INTO foobar (a, b, c, d) VALUES (default, default, default, 1);
+UPDATE foobar
+   SET b = 20
+ WHERE id = 3;
+UPDATE foobar
+   SET c = 25
+ WHERE id = 4;
+DELETE FROM foobar WHERE id = 5;
+UPDATE foobar
+   set id = 5
+ WHERE id = 1;
+
+SELECT * FROM foobar;
+-- should output:
+-- id a  b  c  d
+-- 2  2  3  4  5
+-- 3  3  20 5  6
+-- 4  4  5  25 7
+-- 5  1  2  3  4
+-- 6  6  7  8  9
+-- 7  1         
+-- 8     1      
+-- 9        1   
+-- 10          1