calling function from rule

From: "Tambet Matiisen" <t(dot)matiisen(at)aprote(dot)ee>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: calling function from rule
Date: 2003-01-23 10:51:35
Message-ID: 81132473206F3A46A72BD6116E1A06AE1B14CB@black.aprote.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I have a view and update rule on it, which updates another table. Now I would like to call a function, after update of the table is done. Also the function must be called in the same transaction as update. I tried to define an additional update rule on the view, but doing a SELECT in update rule spoils the affected records count:

hekotek=# create table a(id integer);
CREATE TABLE
hekotek=# create view v as select * from a;
CREATE VIEW
hekotek=# create rule r as on update to v do instead update a set id = new.id where id = old.id;
CREATE RULE
hekotek=# create rule r1 as on update to v do select 1;
CREATE RULE
hekotek=# insert into a values (1);
INSERT 1194985 1
hekotek=# update v set id = 2;
?column?
----------
1
(1 row)

hekotek=# drop rule r1 on v;
DROP RULE
hekotek=# update v set id = 3;
UPDATE 1

It's important to me to have correct affected records count returned. I cannot use triggers, because views can't have triggers. I also cannot define the trigger on the table, because the function must be called only when updated through the view. I think I could define the rule to call the function in update query for some dummy table: update dummy set field = function(parameters). But is there a better way?

Tambet

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ricardo Javier Aranibar León 2003-01-23 14:16:17 Slow query
Previous Message Tomasz Myrta 2003-01-23 10:49:45 Re: To use a VIEW or not to use a View.....