crash.sql

application/sql

Download

-- Cleanup
DROP TRIGGER IF EXISTS temporal_view_delete_trigger
    ON temporal_view;
DROP VIEW IF EXISTS temporal_view;
DROP FUNCTION IF EXISTS temporal_view_instead_delete();
DROP TABLE IF EXISTS temporal_base;



CREATE TABLE temporal_base (
    id       integer,
    valid_at daterange NOT NULL,
    name     text
);

INSERT INTO temporal_base VALUES
    (1, '[2020-01-01,2025-01-01)', 'original row');

CREATE VIEW temporal_view AS
    SELECT id, valid_at, name FROM temporal_base;

CREATE OR REPLACE FUNCTION temporal_view_instead_delete()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    DELETE FROM temporal_base
        WHERE id = OLD.id AND valid_at = OLD.valid_at;
    RETURN OLD;
END;
$$;

CREATE TRIGGER temporal_view_delete_trigger
    INSTEAD OF DELETE ON temporal_view
    FOR EACH ROW EXECUTE FUNCTION temporal_view_instead_delete();

DELETE FROM temporal_view
    FOR PORTION OF valid_at FROM '2022-01-01' TO '2023-01-01'
    WHERE id = 1;