Re: A little RULE help?

On 01/26/2018 04:19 PM, Steven Winfield wrote:
> Back to my original attempt at writing an UPDATE rule…
>
> 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 *;
>
> );
>
> …I wondered whether the pseudo relations NEW and OLD were somehow being
> modified by the first command (the UPDATE), such that the second command
> (INSERT) could not function properly.

No. It's because your view's WHERE clause is being added to your INSERT
and of course nothing matches, so nothing gets inserted.

> That would fit with what I observe, but I’m not sure how I go about
> proving
EXPLAIN

> or fixing it.

Don't use RULEs.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support