Re: SQL:2011 Application Time Update & Delete

On Thu, Apr 16, 2026 at 7:26 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> 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/