Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select now(),version(),setseed(.42);

create schema if not exists rowan;
create table if not exists rowan.employees (
  emp_id integer generated by default as identity primary key, 
  name text, 
  salary numeric);
insert into rowan.employees(name,salary) values 
  ('John', 20000), ('Bob', 60000), ('Dan', 30000)
  returning *;

now
version
setseed

2024-04-30 16:20:56.287655+01
PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE SCHEMA

CREATE TABLE

emp_id
name
salary

1
John
20000

2
Bob
60000

3
Dan
30000

INSERT 0 3

begin;
alter table rowan.employees rename to t_employees;
create view rowan.employees as table rowan.t_employees;

create or replace rule salary_limit as 
  on update to rowan.employees
  do instead update rowan.t_employees 
  set salary = case when new.salary <= 70000 
                      or new.salary is null
                    then new.salary
                    else 70000 end
  where emp_id is not distinct from new.emp_id
  returning *;

commit;

BEGIN

ALTER TABLE

CREATE VIEW

CREATE RULE

COMMIT

update rowan.employees set salary = 60000 
  where emp_id = 3 returning *;
update rowan.employees set salary = null 
  where emp_id = 3 returning *;
update rowan.employees set salary = 80000 
  where emp_id = 3 returning *;

emp_id
name
salary

3
Dan
60000

UPDATE 1

emp_id
name
salary

3
Dan
null

UPDATE 1

emp_id
name
salary

3
Dan
70000

UPDATE 1

table rowan.employees;

emp_id
name
salary

1
John
20000

2
Bob
60000

3
Dan
70000

SELECT 3