PostgreSQL View and Triggers for One-to-One Tables

one_to_one.sql

-- 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.

-- 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;

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