Waiting for 9.1 – triggers on views

On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:

Support triggers ON views.
 
This patch adds the SQL-standard concept OF an INSTEAD OF TRIGGER, which
IS fired instead OF performing a physical INSERT/UPDATE/DELETE.  The
TRIGGER FUNCTION IS passed the entire OLD AND/OR NEW ROWS OF the VIEW,
AND must figure OUT what TO do TO the underlying TABLES TO implement
the UPDATE.  So this feature can be used TO implement updatable views
USING TRIGGER programming STYLE rather than rule hacking.
 
IN passing, this patch corrects the names OF SOME COLUMNS IN the
information_schema.triggers VIEW.  It seems the SQL committee renamed
them somewhere BETWEEN SQL:99 AND SQL:2003.
 
Dean Rasheed, reviewed BY Bernd Helmle; SOME additional hacking BY me.

As you perhaps know, David Fetter argued that there are actually only 2 sensible use-cases for rules, and one of them already has alternative, and if we'd ever want to remove rules (yes, please), we should add something to will take their job with writable views.

So now – we have it.

This is extension of triggers, which can now work “INSTEAD OF" (in addition to previously available BEFORE and AFTER).

These new triggers – INSTEAD OF, can be used only for views (if you'd need anything like this for tables, just use BEFORE trigger, and make it return NULL), and have to be defined as “FOR EACH ROW" (as opposed to: “FOR EACH STATEMENT").

So, how does it work?

Let's see:

$ CREATE TABLE x AS SELECT i, 'value: ' || i AS ti FROM generate_series (1,10) i;
SELECT 10
 
$ SELECT * FROM x;
 i  |    ti
----+-----------
  1 | VALUE: 1
  2 | VALUE: 2
  3 | VALUE: 3
  4 | VALUE: 4
  5 | VALUE: 5
  6 | VALUE: 6
  7 | VALUE: 7
  8 | VALUE: 8
  9 | VALUE: 9
 10 | VALUE: 10
(10 ROWS)
 
$ CREATE VIEW test AS SELECT * FROM x;
CREATE VIEW

And now we can write a trigger. Of course it will be very simplistic, but it's just example:

CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        raise notice 'INSERT trigger, NEW = [%]', NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;
    ELSE
        raise notice 'DELETE trigger, OLD = [%]', OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER test_it INSTEAD OF INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE test_trigger();

Please note that I used “return NULL". This is a signal to PostgreSQL, that the update didn't really happen, so it shouldn't increment row count of modified rows.

Now, let's see how the view \d looks like:

$ \d test 
      VIEW "public.test"
 COLUMN |  TYPE   | Modifiers 
--------+---------+-----------
 i      | INTEGER | 
 ti     | text    | 
Triggers:
    test_it INSTEAD OF INSERT OR DELETE OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE test_trigger()

Nice. Trigger is clearly listed.

So let's try some modifications:

$ INSERT INTO test (ti) VALUES ( 'xx' );
NOTICE:  INSERT TRIGGER, NEW = [(,xx)]
INSERT 0 0
 
$ UPDATE test SET i = i + 123 WHERE i = 3;
NOTICE:  UPDATE TRIGGER, OLD = [(3,"value: 3")], NEW = [(126,"value: 3")]
UPDATE 0
 
$ DELETE FROM test WHERE i = 7;
NOTICE:  DELETE TRIGGER, OLD = [(7,"value: 7")]
DELETE 0

Sweet. Now, with just some sensible logic, you can easily have updateable views, without rules.

8 thoughts on “Waiting for 9.1 – triggers on views”

  1. For my use case, this will probably the most important addition to Postgresql for 9.1. Now I can get front-end applications such as ms-access that only speak SQL-92 to work with >92 data-types once I decompose these data-types in an updateable view. 🙂

  2. Hello. I would like to install this patch. Can you tell me what are the steps?
    Thanks.

  3. @Daniel:

    Not sure I understand your question. This patch is already applied to PostgreSQL 9.1 sources in git.

  4. Hello. I have postgresql 9.0 database and i want to install this patch to this version because in the near future (next week) i want to do migration from oracle to postgres, and i have in Oracle many triggers on views like “instead of insert”.

  5. Blog post contains link to page, which has link to git commit page.

    alternatively you can search in archives of pgsql-hackers mailing list to get the patch itself. But there is no guarantee that it will apply to 9.0 sources.

  6. I installed PostgreSQL 9.1alpha1 and i expect to find this patch integrated in this version of PostgreSql Database. What can i do to upgrade my postgres database with this future?
    Thank you.

Comments are closed.