October 16th, 2010 by depesz | Tags: , , , , , | 9 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 9 comments

  2. # Richard Broersma Jr.
    Oct 17, 2010

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

  3. Oct 17, 2010

    Very good. This is approach is much more “natural” and powerfull for updatetable views.

  4. # Daniel
    Nov 10, 2010

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

  5. Nov 10, 2010

    @Daniel:

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

  6. # Daniel
    Nov 10, 2010

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

  7. Nov 10, 2010

    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.

  8. # Daniel
    Nov 10, 2010

    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.

  9. Nov 10, 2010

    @Daniel:

    get PostgreSQL from git checkout. I suggest that you consult the fine page with information for developers at http://www.postgresql.org/developer/

  10. Jul 27, 2014

    Hi. wollte Dir diese Website gegen langeweile zeigen

Leave a comment