Below is the code I am using in a PostgreSQL 16 database. Every time I try to update the salary I get:
ERROR: infinite recursion detected in rules for relation "employees"
SQL state: 42P17"
Object used
create table rowan.employees (emp_id serial primary key, name varchar(100), salary numeric);
Inserting values
insert into rowan.employees (name, salary) values
('John Doe', 50000),
('Jane Doe', 60000),
('Rafael Orta',80000);
Creation of the rule
create or replace rule update_salary
as on update to rowan.employees
where new.salary > 70000
and pg_trigger_depth() = 0
do instead
update rowan.employees
set salary = 70000
where emp_id = new.emp_id;
Performing the Update
update rowan.employees set salary = 80000 where emp_id = 3;
Checking the values
select * from rowan.employees;
I am trying to do the command below:
update rowan.employees set salary = 80000 where emp_id = 3;
I was expecting for it to update the salary to be 70000.
Listen to the comments and use triggers instead of rules.
If you want to use a rule then a simple example:
create table rule_test (id integer, fld_1 integer);
insert into rule_test values (1, 5), (2, 7);
create view rule_test_vw as select * from rule_test ;
create or replace rule update_do_nothing as on update to rule_test_vw do instead nothing;
create or replace rule update_do_something as on update to rule_test_vw where new.fld_1 > 10 do instead update rule_test set fld_1 = 10 where id = new.id;
create or replace rule update_do_passthru as on update to rule_test_vw where new.fld_1 <= 10 do instead update rule_test set fld_1 = new.fld_1 where id = new.id;
update rule_test_vw set fld_1 = 15 where id = 1;
update rule_test_vw set fld_1 = 3 where id = 2;
select * from rule_test;
id | fld_1
----+-------
1 | 10
2 | 3
Basically create a view over the table and use do instead
against the view with the instead
acting against the table. That will deal with the recursion issue.