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