Re: Tables cannot have INSTEAD OF triggers

On 2015-04-01 13:29:33 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2015-04-01 13:15:26 -0400, Tom Lane wrote:
> >> If you have such a trigger, it's impossible to insert any rows, which
> >> means the table doesn't need storage, which means it may as well be a
> >> view, no? So this still seems to me like a wart not a useful feature.
> >> I think it would create confusion because a table with such a trigger
> >> would act so much unlike other tables.
>
> > For one you can't easily add partitions to a view (and
> > constraint_exclusion = partition IIRC doesn't work if you use UNION ALL),
> > for another there's WHEN for triggers that should allow dealing with
> > that.
>
> WHEN won't help; if there are any INSTEAD OF triggers, no insert will
> happen, whether the triggers actually fire or not.

Well, right now it doesn't work at all. It seems pretty reasonable to
define things so that the insert happens normally if there's no matching
INSTEAD OF trigger.

> As for partitioning, you could do this:
>
> create table parent(...);
> create table child(...) inherits(parent); -- repeat as needed
> create view v as select * from parent;
> attach INSTEAD OF triggers to v
>
> Now the application deals only with v, and thinks that's the real
> table.

Sure, but that's just making things unnecessarily hard. That then
requires also defining UPDATE/DELETE INSTEAD triggers which otherwise
would just work.

Greetings,

Andres Freund