Re: Triggers with DO functionality

On 16 September 2014 13:29, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> On 2014-09-16 13:15:59 +0100, Thom Brown wrote:
> > On 17 February 2012 22:42, Jaime Casanova <jaime(at)2ndquadrant(dot)com> wrote:
> >
> > > On Fri, Feb 17, 2012 at 4:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > >
> > > > Has anybody stopped to look at the SQL standard for this? In-line
> > > > trigger definitions are actually what they intend, IIRC.
> > > >
> > >
> > > this is what i found there
> > >
> > > <trigger definition> ::=
> > > CREATE TRIGGER <trigger name> <trigger action time> <trigger event>
> > > ON <table name> [ REFERENCING <transition table or variable
> list> ]
> > > <triggered action>
> > >
> > > <triggered action> ::=
> > > [ FOR EACH { ROW | STATEMENT } ]
> > > [ WHEN <left paren> <search condition> <right paren> ]
> > > <triggered SQL statement>
> > >
> > > <triggered SQL statement> ::=
> > > <SQL procedure statement>
> > > | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END
> >
> >
> > *slightly delayed response*
> >
> > So it looks like the standard doesn't complicate the proposal from what
> I
> > can tell.
> >
> > Here's our current syntax:
> >
> > CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } {
> event
> > [ OR ... ] }
> > ON table_name
> > [ FROM referenced_table_name ]
> > [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY
> > DEFERRED } ]
> > [ FOR [ EACH ] { ROW | STATEMENT } ]
> > [ WHEN ( condition ) ]
> > EXECUTE PROCEDURE function_name ( arguments )
> >
> > Here's an updated syntax as per the proposal:
> >
> > CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } {
> event
> > [ OR ... ] }
> > ON table_name
> > [ FROM referenced_table_name ]
> > [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY
> > DEFERRED } ]
> > [ FOR [ EACH ] { ROW | STATEMENT } ]
> > [ WHEN ( condition ) ]
> > { EXECUTE PROCEDURE function_name ( arguments )
> > | AS 'trigger function definition' [ LANGUAGE lang_name ]
> > [ SET configuration_parameter { TO value | = value | FROM
> CURRENT }
> > ]
> > }
>
> I'm unconvinced that that's sufficient. You already noticed that you
> need to add SET here. What's with e.g. SECURITY DEFINER? What's with
> AS 'obj_file', 'link_symbol' when you create a C function? I think this
> really would need to incorporate a more fundamental subset of CREATE
> FUNCTION functionality.
>

Fair enough, although others have mentioned that SECURITY DEFINER is pretty
much redundant on trigger functions anyway.

> > The function can't be the target of CREATE OR REPLACE FUNCTION.
>
> That *really* sucks. To the point of making the feature useless in my
> eyes. That's really something frequently done.
>

Why not CREATE OR REPLACE TRIGGER? Wouldn't the function itself be an
internal matter rather than something for users to worry about? If the
user needs to adjust it, they'd need to discover the name of the function
the trigger referred to, which may not be trivial.

>
> > And then there are event triggers, which could have the same
> functionality.
>
> I think the need is much less there. You'll hardly create as many even
> triggers as you create triggers on relations. Doesn't seem worth the
> effort.
>

Agreed, but I thought I'd mention it regardless.

--
Thom