Обновление таблицы с представлением и получение «бесконечная рекурсия, обнаруженная в правилах для отношения «сотрудники»

Возможно, вы захотите использовать для этого триггер, как показано ниже (непроверено):

CREATE FUNCTION cap_salary() RETURNS trigger
AS $$
BEGIN
    IF NEW.salary > 70000
    THEN
        NEW.salary = 70000;
    FI;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cap_salary_on_update
    BEFORE UPDATE ON rowan.employees
    FOR EACH ROW
    EXECUTE FUNCTION cap_salary();

Вызов системы правил является проблемой, поскольку она сначала переписывает запрос, прежде чем что-либо будет выполнено. Условие WHERE будет частью переписанного запроса, но оно по-прежнему включает UPDATE on rowan.employees, т. е. правило выполняется снова — и снова и снова. Обратите внимание, что WHERE будет включен в переписанный запрос и не будет проверен перед переписыванием запроса (это включает в себя то, что все запросы UPDATE будут переписаны, независимо от того, выполнено ли условие).

1. Слушайте комментарии и используйте триггеры вместо правил.

2. Если вы хотите использовать правило, то простой пример:

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

По сути, создайте представление над таблицей и используйте do instead против представления, а instead действует против таблицы. Это решит проблему рекурсии.

Курок

PostgreSQL дешевле проверить условие перед вызовом триггерной функции:

CREATE FUNCTION cap_salary() RETURNS trigger AS $f$ 
BEGIN --trigger won't fire unless the salary needs to be reduced
      NEW.salary = 70000;--so no need for an `IF`
      RETURN NEW;
END $f$ LANGUAGE plpgsql;

CREATE TRIGGER cap_salary_on_update
    BEFORE INSERT OR UPDATE OF salary ON rowan.employees 
    FOR EACH ROW 
    WHEN (NEW.salary > 70000) --this limits when the trigger fires
    EXECUTE FUNCTION cap_salary();

Он достигает той же цели, но вы экономите некоторое время выполнения, избегая более дорогостоящих вызовов PL/pgSQL, когда в них нет необходимости.
Вы можете сравнить выполнение триггера time=1114.413 calls=200003 в этой демо с time=334.140 calls=60373 в этой демо.

Правило

Вам нужен только один: демо

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

create 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;

1. Настоящую таблицу нужно «спрятать» за каким-то другим объектом. Это может быть view, но система правил также может перенаправлять запросы с обычного table. Правило должно перехватывать все запросы, направленные на объект, выдающий себя за таблицу, и перенаправлять их на реальную таблицу.
2. Обработка ограничения зарплаты в операторе case может быть предпочтительнее, чем использование нескольких условных правил. Наличие условия правила не позволяет вам добавить предложение returning, что, в свою очередь, нарушает все вызовы, которые хотели бы его использовать. Некоторые клиентские приложения, библиотеки и ORM добавляют предложение , возвращающее , по умолчанию, независимо от того, используете вы его или нет — нарушение, которое может привести к трудно отслеживаемым ошибкам.
3. Хорошо обрабатывать случай null как для поля salary, так и для поля emp_id. Его необходимо добавить к case, а условие where следует переключить с нулевого небезопасного = на is not distinct from - в противном случае правило не будет обрабатывать null зарплаты или обновления, нацеленные на все строки, особенно на те, у которых есть null в emp_id.
   Последнее не так важно в вашем случае, поскольку emp_id - это primary key, что подразумевает, что это not null - это строго необходимо, когда нет явного или подразумеваемого not null ограничения.
4. Заключаем настройку всего этого в одном: begin;...commit; транзакция позволяет избежать введения временного окна, в течение которого одновременные транзакции
   • временно потерять таблицу из виду сразу после ее переименования
   • получить к нему неограниченный доступ через представление, когда оно появится создано, прежде чем добавлять правило.