Did it help? If yes - maybe you can help me?
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?
$ 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
Column | Type | Modifiers
i | integer |
ti | text |
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.