PostgreSQL view for one-to-one tables.

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

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;
-- expected output:
-- id;a;b;c;d <-- fields
-- 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
--