Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values

On Wed, 22 Feb 2023 at 12:00, Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
>
> Thanks! I understand this place better now.
> I mistakenly supposed that VALUES RTE can intervene with INSERT only.
> But while exploring how they can affect other statements,
> I've found one more interesting thing:
> CREATE TABLE t (a int, b int DEFAULT -1);
> CREATE VIEW v AS SELECT * FROM t;
> CREATE RULE vr AS ON INSERT TO v DO ALSO INSERT INTO t SELECT NEW.b;
> INSERT INTO v VALUES(10, -1), (20, DEFAULT);
> SELECT * FROM v;
>
> a | b
> ----+----
> 10 | -1
> 20 | -1
> -1 | -1
> | -1
>

Yes, that's correct. Or at least that's the way it's always been. We
even have regression tests similar to that.

Any DEFAULT values from the top-level command that haven't been
replaced are set to NULL before the rule is evaluated, so a DEFAULT in
the top-level command becomes a NULL rather than a DEFAULT in the rule
action.

Arguably, that isn't what some users might expect, but it's
long-standing behaviour that would probably be more trouble than it's
worth to change. There are many other surprises in the way rules work,
which is why their use is discouraged, almost to the point of being
deprecated.

Regards,
Dean