Re: questions on rules - Mailing list pgsql-general

From Eric Ridge
Subject Re: questions on rules
Date
Msg-id FE9EF9A0-985D-11D8-AB36-000A95BB5944@tcdi.com
Whole thread Raw
In response to questions on rules  (Timothy Perrigo <tperrigo@wernervas.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Lincoln Yeoh
Date:
Subject: Re: shadowing (like IB/Firebird)
Next
From:
Date:
Subject: Re: shadowing (like IB/Firebird)