Re: Triggers with DO functionality

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 }
]
}

Example:

CREATE TRIGGER trg_my_trigger
BEFORE INSERT ON customers
FOR EACH ROW
AS $$
BEGIN
IF NEW.status IS NULL THEN
...
END;
$$ LANGUAGE plpgsql SET search_path = shop;

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.

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

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.
The function has deptype of 'i' (DEPENDENCY_INTERNAL) so that it can only
be dropped as part of the trigger.
The function can't be the target of ALTER FUNCTION, or if it can, only a
relevant sub-set.
The function can't be the target of CREATE OR REPLACE FUNCTION.

And then there are event triggers, which could have the same functionality.

Thom