Thread: questions on rules

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?

(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');  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

On Monday 26 April 2004 20:12, 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.

I think you want to look at triggers rather than rules here.

--
  Richard Huxton
  Archonet Ltd

Thanks for the reply.  Do you know if triggers defined on a base table
fire for operations on inherited tables?  (I.e., if I have an after
insert trigger on table "base", and a table "derived" that inherits
from base, will inserts into derived cause the trigger on base to
fire?)  If so (this is the behavior I would like), is there a way to
get the tableoid of the table which caused the trigger to fire?

I really appreciate the assistance.

Tim

On Apr 27, 2004, at 7:26 AM, Richard Huxton wrote:

> On Monday 26 April 2004 20:12, 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.
>
> I think you want to look at triggers rather than rules here.
>
> --
>   Richard Huxton
>   Archonet Ltd
>

On Tuesday 27 April 2004 13:40, Timothy Perrigo wrote:
> Thanks for the reply.  Do you know if triggers defined on a base table
> fire for operations on inherited tables?  (I.e., if I have an after
> insert trigger on table "base", and a table "derived" that inherits
> from base, will inserts into derived cause the trigger on base to
> fire?)

Hmm - don't know this I'm afraid.

>  If so (this is the behavior I would like), is there a way to
> get the tableoid of the table which caused the trigger to fire?

Here I can help. Check the plpgsql section of the manuals, and there you'll
find a list of special variables available to trigger functions. These
include table and trigger name.

--
  Richard Huxton
  Archonet Ltd

It seems that triggers are not inherited, so to get the functionality I
want I'll have to create a trigger for each table.  If anyone knows
another way, please let me know!

After you pointed me in the right direction, I was able to create a
trigger procedure which can be called from triggers on various tables
and will log the operation (including the affected table's oid and
name).  The procedure is listed below.  Thanks for the help!

Tim

create or replace function add_log_entry() returns TRIGGER as '
BEGIN
insert into audit_log(table_oid, table_name, id, operation) values
(TG_RELID, TG_RELNAME, NEW.id, TG_OP);
return NEW;
END;
' language 'plpgsql';

On Apr 27, 2004, at 8:18 AM, Richard Huxton wrote:

> On Tuesday 27 April 2004 13:40, Timothy Perrigo wrote:
>> Thanks for the reply.  Do you know if triggers defined on a base table
>> fire for operations on inherited tables?  (I.e., if I have an after
>> insert trigger on table "base", and a table "derived" that inherits
>> from base, will inserts into derived cause the trigger on base to
>> fire?)
>
> Hmm - don't know this I'm afraid.
>
>>  If so (this is the behavior I would like), is there a way to
>> get the tableoid of the table which caused the trigger to fire?
>
> Here I can help. Check the plpgsql section of the manuals, and there
> you'll
> find a list of special variables available to trigger functions. These
> include table and trigger name.
>
> --
>   Richard Huxton
>   Archonet Ltd
>

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