Re: Tables cannot have INSTEAD OF triggers

On Wed, Apr 1, 2015 at 12:04 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2015-04-01 11:40:13 -0400, Robert Haas wrote:
>> I don't see how this helps. The problem with partitioning is that you
>> need a way to redirect the INSERT to another table, and there's no
>> built-in way to do that, so you have to simulate it somehow. That
>> issue seems largely separate from how the CREATE TRIGGER command is
>> spelled. Maybe I'm missing something.
>
> Without INSTEAD OF you can't, to my knowledge, return a valid tuple from
> the top level table without also inserting into it. Returning NULL after
> redirecting the tuple into a child table will break RETURNING; not
> returning NULL will insert the tuple in the top level table.
>
> So the only way to do redirection that doesn't break RETURNING without
> rules is to insert the tuple in the child in the BEFORE trigger return
> NEW and delete the top level table row in an AFTER trigger. That sucks.

So, the idea is that INSTEAD OF would behave like BEFORE but the tuple
it returns wouldn't actually be inserted? That wasn't clear to me
from the OP, but I guess it would be a reasonable way to go.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company