Home - LegitimateSounding.com

Stupid PostgreSQL Tricks: Writable Views

Thursday, August 19, 2010 - Jerry Sievert

When needing two or more fairly disparate systems to work together seamlessly, having complete flexibility at the database level can be a blessing.

Take for instance the problem of a ten year old legacy system hosting millions of accounts, and an up to date content management system that needs complete access to that data as if it were its own. You can manage multiple systems with complicated triggers, methods for moving data around, expensive joins, funky stored procedures, hacks to the code, or you can simply use a writable view.

Let's start with the legacy system (schemas have been simplified):

CREATE SEQUENCE legacy_login_id_seq;

CREATE TABLE legacy_login
(
  legacy_login_id INT DEFAULT nextval('legacy_login_id_seq'),
  email VARCHAR(256),
  password VARCHAR(64),
  created TIMESTAMP,
  firstname VARCHAR(64),
  lastname VARCHAR(64)
);

Add in the new-fangled content management:

CREATE TABLE new_fangled
(
  id INT,
  username VARCHAR(256),
  password VARCHAR(64),
  created_time INT
);

And you find yourself with a little bit of a mess; field names don't quite match up, table names are different, and types have a fundamental mismatch. A view can help to some extent:

CREATE VIEW new_fangled (id, username, password) AS
   SELECT legacy_login_id AS id,
          email AS username,
          password,
          CAST(EXTRACT(epoch FROM created) AS INT) AS created_time
     FROM legacy_login;

But this only gives you read access. The interesting thing about how PostgreSQL implements views is that they are implemented as rules on SELECTs. Thus, the above view can be rewritten by creating a table and implementing a rule:

CREATE TABLE new_fangled
(
  id INT,
  username VARCHAR(128),
  password VARCHAR(64),
  created_time INT
);

CREATE OR REPLACE RULE "_RETURN" AS
  ON SELECT TO new_fangled
  DO INSTEAD
  SELECT legacy_login_id AS id,
         email AS username,
         password,
         CAST(EXTRACT(epoch FROM created) AS INT) AS created_time
    FROM legacy_login;

Rule based view

The key here is the do instead: the rule replaces the existing plan with a new plan based on the replacement SELECT. To achieve both read and write access, you can use additional rules on the same table (one of the more mainstream uses of these same rules is PostgreSQL's version of partitioning via table inheritance):

CREATE OR REPLACE RULE insert_new_fangled_table AS
  ON INSERT TO new_fangled
  DO INSTEAD
     INSERT INTO legacy_login (legacy_login_id, email, password, created)
     VALUES (nextval('legacy_login_id_seq'),
             NEW.username,
             NEW.password,
             TIMESTAMP WITH TIME ZONE 'epoch' + NEW.created_time * INTERVAL '1 second');

Insert Rule

CREATE OR REPLACE RULE update_new_fangled_table AS
  ON UPDATE TO new_fangled
  DO INSTEAD
     UPDATE legacy_login
        SET email = NEW.username,
            password = NEW.password,
            created = TIMESTAMP WITH TIME ZONE 'epoch' + NEW.created_time * INTERVAL '1 second'
      WHERE legacy_login_id = OLD.id;

Update Rule

CREATE OR REPLACE RULE delete_new_fangled_table AS
  ON DELETE TO new_fangled
  DO INSTEAD
     DELETE FROM legacy_login
      WHERE legacy_login_id = OLD.id;

Delete Rule

Using these rules we are able to remap all INSERTs, UPDATEs, and DELETEs with do instead rules allowing for what amounts to a writable view. These rules are extremely flexible, and can utilize stored procedures making them very powerful.