On Thu, Apr 16, 2026 at 7:26 AM Paul A Jungwirth <[email protected]> wrote: > > I think using INSTEAD OF triggers to replace an UPDATE/DELETE FOR > PORTION OF is a valid use-case, but it doesn't make sense to insert > temporal leftovers. As you say, we can't access the underlying > storage. But also we don't know what changes the trigger actually > made. The trigger should be responsible for leftovers, and we > shouldn't try to add more. So I think the fix is just to skip > inserting leftovers. I've attached a patch to do that. > hi.
CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NEW;
END;
$$;
CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO
'2024-08-01'
SET val = 999 WHERE id = 1
RETURNING *;
id | valid_at | val
----+-------------------------+-----
1 | [2024-01-01,2024-12-31) | 999
(1 row)
Should I expect the column `valid_at` value as [2024-04-01,2024-08-01) ?
We should also document this on doc/src/sgml/ref/update.sgml
Attached is a minor regession test enhancement for
"v2-0001-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PO.patch".
--
jian
https://www.enterprisedb.com/
v3-0001-misc-fix-for-V2-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR.no-cfbot
Description: Binary data
