Re: questions on rules

On Apr 26, 2004, at 3:12 PM, Timothy Perrigo wrote:

> 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