count number of rows to be affected before update in trigger

Question

I want to know number of rows that will be affected by UPDATE query in BEFORE per statement trigger . Is that possible?

The problem is that i want to allow only queries that will update up to 4 rows. If affected rows count is 5 or more i want to raise error.

I don't want to do this in code because i need this check on db level. Is this at all possible?

Thanks in advance for any clues on that

Answer 1

Write a function that updates the rows for you or performs a rollback. Sorry for poor style formatting.

create function update_max(varchar, int)
RETURNS void AS
$BODY$

DECLARE

   sql ALIAS FOR $1;
   max ALIAS FOR $2;
   rcount INT;

BEGIN

   EXECUTE sql;
   GET DIAGNOSTICS rcount = ROW_COUNT;

   IF rcount > max THEN

       --ROLLBACK;
       RAISE EXCEPTION 'Too much rows affected (%).', rcount;

   END IF;

   --COMMIT;

END;

$BODY$ LANGUAGE plpgsql

Then call it like

select update_max('update t1 set id=id+10 where id < 4', 3);

where the first param ist your sql-Statement and the 2nd your max rows.

Answer 2

Simon had a good idea but his implementation is unnecessarily complicated. This is my proposition:

create or replace function trg_check_max_4()                
returns trigger as $$
begin
        perform true from pg_class 
                where relname='check_max_4' and relnamespace=pg_my_temp_schema(); 
        if not FOUND then
                create temporary table check_max_4 
                        (value int check (value<=4)) 
                        on commit drop;
                insert into check_max_4 values (0); 
        end if;

        update check_max_4 set value=value+1; 
        return new;
end; $$ language plpgsql;

Answer 3

I've created something like this:

begin;

create table test (
    id integer
);

insert into test(id) select generate_series(1,100);


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';
    select count(42) into counter_ 
        from pg_catalog.pg_tables where tablename = tablename_;
    if counter_ = 0 then
        raise notice 'Creating table %', tablename_;
        execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
        execute 'insert into ' || tablename_ || ' (counter) values(1)';

        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;
    else
        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;

    end if;
    return new;
end; $$ language plpgsql;


create trigger trg_bu_test before 
  update on test 
  for each row
  execute procedure trg_check_max_4_updated_records();

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;

rollback;

The main idea is to have a trigger on 'before update for each row' that creates (if necessary) a temporary table (that is dropped at the end of transaction). In this table there is just one row with one value, that is the number of updated rows in current transaction. For each update the value is incremented. If the value is bigger than 4, the transaction is stopped.

But I think that this is a wrong solution for your problem. What's a problem to run such wrong query that you've written about, twice, so you'll have 8 rows changed. What about deletion rows or truncating them?

Answer 4

PostgreSQL has two types of triggers: row and statement triggers. Row triggers only work within the context of a row so you can't use those. Unfortunately, "before" statement triggers don't see what kind of change is about to take place so I don't believe you can use those, either.

Based on that, I would say it's unlikely you'll be able to build that kind of protection into the database using triggers, not unless you don't mind using an "after" trigger and rolling back the transaction if the condition isn't satisfied. Wouldn't mind being proved wrong. :)