How to Allow For Max 4 Rows in Update (PostgreSQL)
A question found on the net:
how to restrict users to update only max 4 rows of a table in a transaction.
This looks like a solution to some artificial problem that doesn’t exist, but nevermind, any task should be solved.
The Solution
The solution is simple, there is a trigger on the table. First the table:
create table test (
id integer
);
and some data:
insert into test(id) select generate_series(1,100);
Now the trigger. It is quite simple:
- There is a temporary table created by the trigger (only if needed).
- The table should be dropped at commit. At rollback it is dropped automatically.
- This temporary table has just one column and one row – this is the counter.
- The counter is used for storing information about the number of rows updated in current transaction.
- If the number is greater than 4, just rollback the transaction.
create or replace function trg_check_max_4_updated_records()
returns trigger as $$
declare
counter_ integer := 0;
tablename_ text := 'temptable';
begin
raise notice 'trigger fired';
begin
raise notice 'Creating table %', tablename_;
execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
execute 'insert into ' || tablename_ || ' (counter) values(0)';
execute 'select counter from ' || tablename_ into counter_;
raise notice 'Actual value for counter= [%]', counter_;
exception
when duplicate_table then
null;
end;
execute 'select counter from ' || tablename_ into counter_;
execute 'update ' || tablename_ || ' set counter = counter + 1';
raise notice 'updating';
execute 'select counter from ' || tablename_ into counter_;
raise notice 'Actual value for counter= [%]', counter_;
if counter_ > 4 then
raise exception 'Cannot change more than 4 rows in one trancation';
end if;
return new;
end; $$ language plpgsql;
A little bit of testing:
update test set id = 10 where id = 1;
update test set id = 10 where id = 2;
update test set id = 10 where id = 3;
update test set id = 10 where id = 4;
update test set id = 10 where id = 5;
the output:
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: Creating table temptable
psql:test.sql:45: NOTICE: Actual value for counter= [1]
UPDATE 1
psql:test.sql:46: NOTICE: trigger fired
psql:test.sql:46: NOTICE: updating
psql:test.sql:46: NOTICE: Actual value for counter= [2]
UPDATE 1
psql:test.sql:47: NOTICE: trigger fired
psql:test.sql:47: NOTICE: updating
psql:test.sql:47: NOTICE: Actual value for counter= [3]
UPDATE 1
psql:test.sql:48: NOTICE: trigger fired
psql:test.sql:48: NOTICE: updating
psql:test.sql:48: NOTICE: Actual value for counter= [4]
UPDATE 1
psql:test.sql:49: NOTICE: trigger fired
psql:test.sql:49: NOTICE: updating
psql:test.sql:49: NOTICE: Actual value for counter= [5]
psql:test.sql:49: ERROR: Cannot change more than 4 rows in one trancation
One more test, updating 10 rows in one query:
update test set id = 10 where id <= 10;
and the output:
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: Creating table temptable
psql:test.sql:45: NOTICE: Actual value for counter= [1]
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: updating
psql:test.sql:45: NOTICE: Actual value for counter= [2]
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: updating
psql:test.sql:45: NOTICE: Actual value for counter= [3]
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: updating
psql:test.sql:45: NOTICE: Actual value for counter= [4]
psql:test.sql:45: NOTICE: trigger fired
psql:test.sql:45: NOTICE: updating
psql:test.sql:45: NOTICE: Actual value for counter= [5]
psql:test.sql:45: ERROR: Cannot change more than 4 rows in one trancation
So it works but I still think that it is a solution for a nonexistent problem, but I don’t know what the problem is.