Updating a table with a view and getting "infinite recursion detected in rules for relation "employees"

Question

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.

Answer 1

1. Listen to the comments and use triggers instead of rules.

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