Re: Tables cannot have INSTEAD OF triggers

On 2015-04-01 11:40:13 -0400, Robert Haas wrote:
> On Tue, Mar 31, 2015 at 8:49 AM, Aliouii Ali <aliouii(dot)ali(at)aol(dot)fr> 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.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services