questions on rules

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 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 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.