LShift Ltd.

Updateable views in PostgreSQL

July 20th, 2006 matthias

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

Entry Filed under: Technology, Programming