instead_delete.sql

text/plain

Filename: instead_delete.sql

Type: text/plain

Part: 0

Message: Allow INSTEAD OF DELETE triggers to modify the tuple for RETURNING

BEGIN;

CREATE OR REPLACE FUNCTION foof() RETURNS TRIGGER AS $$
BEGIN
-- imagine someone concurrently incremented counter here
OLD.counter := OLD.counter + 1;
RETURN OLD;
END
$$ LANGUAGE plpgsql;

CREATE TABLE foo(counter int NOT NULL);

CREATE VIEW foov AS SELECT * FROM foo;

CREATE TRIGGER foov_instead
INSTEAD OF DELETE ON foov
FOR EACH ROW
EXECUTE PROCEDURE foof();

INSERT INTO foo VALUES (0);

DELETE FROM foov RETURNING counter;

ROLLBACK;