Stupid PostgreSQL Tricks: Writable ViewsThursday, August 19, 2010
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;
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');
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;
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;
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.