Re: Trigger with WHEN clause (WIP)

On Fri, 2009-10-16 at 10:14 +0900, Itagaki Takahiro wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> > > I think there is a benefit to provide WHEN cluase at least
> > > for compatibility with other DBMSs, even through we can move
> > > the expressions into the body of trigger functions.
> >
> > This seems to me to be a lot of code to accomplish nothing useful.
> > It will always be the case that any nontrivial logic has to be done
> > inside the trigger. And the compatibility argument is entirely
> > pointless given the lack of compatibility in the trigger function
> > itself.
>
> I see that WHEN cluase is not always needed,
> but I think there are several benefits to have it:
>
> * WHEN cluase is in SQL standard.
>
> * We could recheck trigger conditions when NEW tuple is modified.
> (not yet implemented in the patch, though)
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg00286.php
>
> * As for compatibility, it is easy for typical users to move trigger
> bodies into a function, but they don't like to merge the condition
> and the bodies into a function in my experience.

+1 because

* It is SQL Standard
* Other RDBMS support it (Oracle, DB2)
* It will reduce size of in-memory pending trigger list (for large
statements) and avoid invoking run-time of function handlers, important
for heavier PLs (for small statements)

I also support addition of INSTEAD OF triggers for first two reasons and
because it may be an easier route to allow updateable views.

--
Simon Riggs www.2ndQuadrant.com