Updateable views in PostgreSQL

By: on July 20, 2006

In one of our projects I needed to do some processing on data stored
in a PostgreSQL database. The data contains timestamps but the
processing requires time to be represented as seconds since the
epoch. What to do?

Generally, date&time processing is major headache. There are just way
too many opportunities to get things wrong. In particular, obtaining
the right result when the data has been passed through several layers
of conversion – database, database driver, o/r layer, programming
language – is fraught with difficulty. So I usually try to do the
conversions as close to the source as possible. In this instance that
means doing the conversion in the database. The quick solution is to
construct an appropriate SQL query that does the conversion. A better
idea though is to create a view. Here’s what I ended up with:

CREATE VIEW intervals AS
SELECT t.id as id,
t.user_id as user_id,
t.task_id as task_id,
CAST(EXTRACT(EPOCH FROM t.start_time AT TIME ZONE ‘UTC’)) as start_time,
CAST(EXTRACT(EPOCH FROM t.end_time AT TIME ZONE ‘UTC’) as end_time
FROM task_time t;

This works all very well as long as all we want to do is *retrieve*
data. What about updates? It turns out that the PostgreSQL rule system
allows us to make the above view behave like an ordinary table, with
insert, update and delete all working as expected. The
[documentation](http://www.postgresql.org/docs/8.1/interactive/rules-update.html)
of this feature is excellent, and with its help it took me just a few
minutes to produce the following:

CREATE RULE intervals_ins AS ON INSERT TO intervals
DO INSTEAD
INSERT INTO task_time VALUES(
DEFAULT,
NEW.user_id,
NEW.task_id,
TIMESTAMP ‘epoch’ + NEW.start_time * INTERVAL ‘1 second’,
TIMESTAMP ‘epoch’ + NEW.end_time * INTERVAL ‘1 second’);

CREATE RULE intervals_upd AS ON UPDATE TO intervals
DO INSTEAD
UPDATE task_time
SET id = NEW.id,
user_id = NEW.user_id,
task_id = NEW.task_id,
start_time = TIMESTAMP ‘epoch’ + NEW.start_time * INTERVAL ‘1 second’,
end_time = TIMESTAMP ‘epoch’ + NEW.end_time * INTERVAL ‘1 second’
WHERE id = OLD.id;

CREATE RULE intervals_del AS ON DELETE TO intervals
DO INSTEAD
DELETE FROM task_time
WHERE id = OLD.id;

With the above in place my code simply accesses the `intervals` table
for all operations that previously involved the `task_time`
table, and all time format conversions are done behind the scenes in
the database.

Share

Comment

  1. Seb says:

    Thanks for the example! I find these rules quite difficult to write when we have more complicated views, like those having one-sided joins, where an update may require updating and inserting at the same time. I wish there was a bit more guidance in the docs regarding these kind of views.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*