How To Restrict Delete using PL/pgSQL trigger?

Question

If the client user is trying to delete more than 5 records from a Table i want to restrict that using a trigger. I have a basic idea to do that but i don't know how to implement the Idea. I appreciate any HELP.

Basic Idea : In Trigger IF TG_OP = Delete and the count of records to be deleted are more than 5 then Restrict.

CREATE TRIGGER adjust_count_trigger BEFORE DELETE ON schemaname.tablename
FOR EACH ROW EXECUTE PROCEDURE public.adjust_count();

CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$$
    DECLARE
        num_rows int;
        num_rows1 int;

    BEGIN

        IF TG_OP = 'DELETE' THEN

            EXECUTE 'select count(*) from '||TG_TABLE_SCHEMA ||'.'||TG_RELNAME ||' where oid = old.oid ' into num_rows  ;


            IF num_rows > 5   Then

            RAISE NOTICE 'Cannot Delete More than 5 Records , % ', num_rows ;

            END IF ;


        END IF ;

        RETURN OLD;
    END;
$$
LANGUAGE 'plpgsql';

Answer 1

In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10. You need two triggers.

create trigger before_delete 
before delete on my_table
for each row execute procedure before_delete();

create trigger after_delete 
after delete on my_table
for each statement execute procedure after_delete();

In the first trigger create a temp table and insert a row into it:

create or replace function before_delete()
returns trigger language plpgsql as $$
begin
    create temp table if not exists deleted_rows_of_my_table (dummy int);
    insert into deleted_rows_of_my_table values (1);
    return old;
end $$;

In the other trigger count rows of the temp table and drop it:

create or replace function after_delete()
returns trigger language plpgsql as $$
declare
    num_rows bigint;
begin
    select count(*) from deleted_rows_of_my_table into num_rows;
    drop table deleted_rows_of_my_table;
    if num_rows > 5 then
        raise exception 'Cannot Delete More than 5 Records , % ', num_rows;
    end if;
    return null;
end $$;

The above solution may seem a bit hacky but it is safe if only the temp table does not exist before delete (do not use the same name of the temp table for multiple tables).

Test it in rextester.