Hi,
Isn't it possible to change the data of a row from it's trigger
function?
I would like to have some update statistics in a database. So I
creeated a basetable with a number_of_updates attribute. Then I
created a child tables, that inherits the attributes. In order to
update the attributes I wrote a trigger function for the updates. But
it does not work. Is there something missing or is this not possible?
This shows what I have done:
----------------------------------------------------------------------
users=> CREATE TABLE basetable
users-> (
users-> created datetime DEFAULT 'now',
users-> last_modified datetime DEFAULT 'now',
users-> last_syncronized datetime DEFAULT '-infinity',
users-> number_of_updates int DEFAULT 0
users-> );
CREATE
users=>
users=> CREATE FUNCTION basetable_update() RETURNS opaque AS '
users'> BEGIN
users'> new.last_modified := now();
users'> new.number_of_updates := old.number_of_updates + 1;
users'> RETURN new;
users'> END;
users'> ' LANGUAGE 'plpgsql';
CREATE
users=>
users=> CREATE TABLE toptable
users-> (
users-> somedata1 text,
users-> somedata2 text
users-> )
users-> INHERITS (basetable);
CREATE
users=>
users=> CREATE TRIGGER toptable_update after UPDATE
users-> ON toptable FOR EACH ROW EXECUTE PROCEDURE basetable_update();
CREATE
users=>
users=> insert into toptable (somedata1) values ('jau');
INSERT 341407 1
users=> select somedata1, last_modified, number_of_updates from toptable;
somedata1|last_modified |number_of_updates
---------+----------------------------+-----------------
jau |Thu Dec 02 13:34:32 1999 MET| 0
(1 row)
users=> update toptable set somedata1 = 'jau neu' where somedata1 = 'jau';
UPDATE 1
users=> select somedata1, last_modified, number_of_updates from toptable;
somedata1|last_modified |number_of_updates
---------+----------------------------+-----------------
jau neu |Thu Dec 02 13:34:32 1999 MET| 0
(1 row)
----------------------------------------------------------------------
The number_of_updates attribute does not change, although the
basetable_update function writes it. Can anybody show me the error?
Sascha
