A little RULE help? - Mailing list pgsql-general

Hi all,

I’m trying to create a temporal table, from which data is never really deleted but each row “exists” for only a certain period of time, and a view of this table showing the currently “live” rows.

I would also like the view to accept INSERT, UPDATE, and DELETEs, and perform the relevant operations on the parent table.

I have got everything working by using RULEs on the view, except for UPDATES, where every attempt has failed – my UPDATE rule seems to have the same effect on the table as a DELETE, but no rows are returned when using a RETURNING clause. I can’t see what I’m doing wrong.

I could use a TRIGGER instead (as shown below) but I’d rather not incur the extra overhead if there is a RULE out there that works.

I’m running v10.1, but this example should be compatible with at least v9.6.

Any help would be greatly appreciated!

Thanks,

Steve.

Here is some code to set up the example

CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS rule_test CASCADE;

CREATE TABLE rule_test (

        id serial PRIMARY KEY,

        tt tstzrange NOT NULL,

        foo integer NOT NULL,

        bar double precision NOT NULL,

        EXCLUDE USING gist (tt WITH &&, foo WITH =)

);

CREATE TABLE rule_test_view ( LIKE rule_test );

CREATE RULE "_RETURN" AS ON SELECT TO rule_test_view DO INSTEAD

        SELECT * FROM rule_test WHERE tt @> CURRENT_TIMESTAMP;

CREATE RULE rule_test_insert AS ON INSERT TO rule_test_view DO INSTEAD

        INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;

CREATE RULE rule_test_delete AS ON DELETE TO rule_test_view DO INSTEAD

        UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id RETURNING *;

-- What I'd like to be able to do here is this:

-- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (

--         UPDATE rule_test SET tt = tstzrange(lower(tt), CURRENT_TIMESTAMP, '[)') WHERE id = OLD.id;

--         INSERT INTO rule_test (tt, foo, bar) VALUES (tstzrange(CURRENT_TIMESTAMP, NULL, '[)'), NEW.foo, NEW.bar) RETURNING *;

-- );

-- or, using the rules already defined, this:

-- CREATE RULE rule_test_update AS ON UPDATE TO rule_test_view DO INSTEAD (

--         DELETE FROM rule_test_view WHERE id = OLD.id;

--         INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, NEW.bar) RETURNING *;

-- );

-- but I can only get the desired effect using this trigger:

CREATE OR REPLACE FUNCTION rule_test_update ()

RETURNS trigger

AS

$$

BEGIN

        DELETE FROM rule_test_view WHERE id = OLD.id;

        INSERT INTO rule_test_view (foo, bar) VALUES (NEW.foo, NEW.bar) RETURNING tt into NEW.tt;

        RETURN NEW;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER rule_test_update_trig INSTEAD OF UPDATE ON rule_test_view FOR EACH ROW EXECUTE PROCEDURE rule_test_update();

…And here is some code to test DML on the view

-- ###########################

-- 1. Insert some data, and check that RETURNING works

INSERT INTO rule_test_view (foo, bar) VALUES (1, 1.0), (2, 2.0), (3, 3.0) RETURNING *;

-- 2. Check that tt has been populated for each row

TABLE rule_test;

-- 3. Check that all rows are visible in the view

TABLE rule_test_view;

-- 4. "Delete" one row, and check that RETURNING works

DELETE FROM rule_test_view WHERE id = 1 RETURNING *;

-- 5. Check that the row still exists in the table, but the upper limit of tt is now set

TABLE rule_test;

-- 6. Check that the "deleted" row is no longer visible in the view

TABLE rule_test_view;

-- 7. "Update" one row, and check that RETURNING works

UPDATE rule_test_view SET bar = 3.141 WHERE foo = 3 RETURNING *;

-- 8. Check that the old version still exists in the table, but the upper limit of tt is now set,

--    and a new version now exists, holding the new value

TABLE rule_test;

-- 9. Check that the old version of the row is no longer visible in the view

TABLE rule_test_view;