Re: Triggers with DO functionality

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.

> 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