On 17 February 2012 22:42, Jaime Casanova <ja...@2ndquadrant.com> wrote:
> On Fri, Feb 17, 2012 at 4:46 PM, Tom Lane <t...@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 } ] } 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