Re: Triggers with DO functionality - Mailing list pgsql-hackers

On 2014-09-16 13:15:59 +0100, Thom Brown wrote:
> On 17 February 2012 22:42, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> 
> > On Fri, Feb 17, 2012 at 4:46 PM, Tom Lane <tgl@sss.pgh.pa.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.

> All anonymous trigger functions would be implicitly volatile.  I imagine
> that the function would need to be "owned" by the trigger, meaning the
> function is dropped with the trigger.

Right, that's necessary.

> So should this then just create a function named after the trigger, perhaps
> with a leading underscore? (e.g. _trg_my_trigger)

Hm...

> I would expect that the only differences between this and a regular
> trigger-function pair would be:
> 
> The function is auto-generated and named after the trigger.

ok.

> The function has deptype of 'i' (DEPENDENCY_INTERNAL) so that it can only
> be dropped as part of the trigger.

ok.

> The function can't be the target of ALTER FUNCTION, or if it can, only a
> relevant sub-set.

ok.

> 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.

> 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.

Greetings,

Andres Freund