Re: Why no INSTEAD OF triggers on tables?

On Tue, Dec 17, 2013 at 3:27 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> On 12/17/13, 4:53 AM, Robert Haas wrote:
>>>
>>> Well, I'm specifically thinking of master partition tables. In that
>>> case, we really want an INSTEAD OF trigger.
>>
>> /me scratches head.
>>
>> So, put a BEFORE trigger, and make it return NULL. Same effect,
>> different notation.
>
>
> But it's not the same effect at all, that's the point:
>
> =# create view foov as select 1 as a;
> CREATE VIEW
>
> =# create function insteadof() returns trigger as $$
> $# begin
> $# -- INSERT here
> $# return new;
> $# end
> $# $$ language plpgsql;
> CREATE FUNCTION
>
> =# create function before() returns trigger as $$
> $# begin
> $# -- INSERT here
> $# return null;
> $# end
> $# $$ language plpgsql;
> CREATE FUNCTION
>
> =# create trigger t1 instead of insert on foov for each row execute
> procedure insteadof();
> CREATE TRIGGER
>
> =# create trigger t2 before insert on bart for each row execute procedure
> before();
> CREATE TRIGGER
>
> =# insert into foov values (1) returning *;
> a
> ---
> 1
> (1 row)
>
> INSERT 0 1
>
> local:marko=#* insert into bart values (1) returning *;
> a
> ---
> (0 rows)
>
> INSERT 0 0

Ah, interesting point. I didn't realize it worked like that. That
does seem like a mighty useful thing to be able to do.

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