How to delete rows out-of-transaction just in one PostgreSQL trigger function

Viewed 291

I would like to know if it is possible to run a query where only records already recorded outside the current transaction are returned. The objective is to allow, when updating a table, to remove old records from another table without interfering with the insertion of current records that are possibly inserted in the same transaction.

create table entity_authorization(id varchar);
create table entity (entity_field varchar);

CREATE or REPLACE FUNCTION revoke_obsolete_autorization_entity()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
  --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; --ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
    DELETE from entity_authorization WHERE TRUE; -- i need remove just rows exists on database before this transaction
  RETURN new;
END
$$;

CREATE or REPLACE FUNCTION request_autorization_entity()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    PERFORM 1 from entity_authorization;
    if not found then
        raise EXCEPTION 'not allowed';
    end if;
    RETURN new;
END
$$;

CREATE TRIGGER revoke_obsolete_autorization
    BEFORE UPDATE
    ON entity
    FOR EACH ROW
EXECUTE PROCEDURE revoke_obsolete_autorization_entity();

CREATE CONSTRAINT TRIGGER request_autorization
    AFTER UPDATE
    ON entity
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
EXECUTE PROCEDURE request_autorization_entity();

--INSERTS
DELETE from entity_authorization WHERE TRUE;
DELETE FROM entity WHERE TRUE;
INSERT INTO entity_authorization VALUES('AAA');
INSERT INTO entity VALUES ('value');

--Transaction Test
BEGIN;
INSERT INTO entity_authorization VALUES ('AAA'), ('BBB'); --don't change this to after 'update' line
UPDATE entity SET entity_field = 'new_value' WHERE entity_field = 'value'; -- error because have zero rows in entity_authorization
COMMIT;
0 Answers
Related