questions on rules

[Timothy Perrigo]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Timothy Perrigo]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Timothy Perrigo]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Timothy Perrigo]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Timothy Perrigo]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Eric Ridge]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Eric Ridge]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend