I'm trying to set up some basic rules to log inserts, updates, and deletes to tables in an inheritance hierarchy (by inserting records into a log table), and I've got a couple of questions.
(1) Is it possible to create a rule on a base table and have it operate for all derived tables? I'd like to just create 3 rules (insert/update/delete) on the base table and have them apply to all inherited tables. Can this be done?
I've never tried this myself, but I feel pretty good about saying the answer is "NO". :( Most other postgres features (esp. triggers) don't inherit either.
(2) I've got a very simple update rule-- create rule log_updates as on update to foo do insert into audit_log(table_oid, id, log_what) values (foo.tableoid, NEW.foo_id, 'U');
Ever just tried to do this from psql: SELECT foo.tableoid;
You get a resultset with a row for every row in table foo. That's essentially what your INSERT statement is doing. It's as if you wrote:
INSERT INTO audit_log(table_oid, id, what) SELECT tableoid, NEW.foo_id, 'U' FROM foo;
What you want to do in your rule, I think, is something like this:
INSERT INTO audit_log(table_oid, id, what) values ( (select tableoid from foo limit 1), NEW.foo_id, 'U');
There might be a different way to lookup the tableoid for table "foo", but it would likely require using 'foo' as a quoted string against a query in pg_class, so the above might make things clearer.
eric
ps, never knew about the "tableoid" field until just now. how interesting.
I had hoped that this would create a single entry in my audit_log table for each row updated. However, it seems to fire for each record in the "foo" table, even if the update affected only one row! What am I doing wrong?
Any help would be very much appreciated. Thanks!
Tim Perrigo
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
