Row and Statement Level Rules
The FOR EACH clause optionally allows defining a row or statement rule; FOR EACH ROW is the default and the only option for BEFORE rules.
When the row level rule is executed, a parameter list is built and the procedure is invoked for each row touched by the statement. If a single DELETE or UPDATE statement affects 100 rows, the procedure invocation occurs 100 times.
When the statement level rule is executed, the parameters passed in the procedure invocation for each qualifying row of the triggering statement are accumulated in an internal temporary table. The temporary table, containing information from all rows touched by the triggering statement, is passed with a single call to the rule procedure. This can potentially save many calls to the rule procedure.
All qualifying rows contained in an internal temporary table are processed by the triggering statement so that the rule procedure is invoked just once.
Examples of both row and statement level rules follow.
In this example, a row level rule (the default) executes the ruleproc1 procedure for every INSERT INTO table_x WHERE col1 > 5000:
create rule r1 after insert into table_x where new.col1> 5000
execute procedure ruleproc1 (p1 = new.col1, p2 = new.col5);
The following example is an exact equivalent of the preceding one; either version can be used:
create rule r1 after insert into table_x where new.col1> 5000
for each row execute procedure ruleproc1 (p1 = new.col1, p2 = new.col5);
In this example, a statement level rule executes the procedure ruleproc2 after each delete of table_y. The col1 and col2 values for each row deleted by a single statement are accumulated in an internal temporary table and are passed together to ruleproc2 with a single call:
create rule r2 after delete from table_y
for each statement execute procedure ruleproc2 (q1 = old.col1, q2 = old.col2);