From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Jaime Casanova <jaime(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, Peter Eisentraut <peter_e(at)gmx(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Triggers with DO functionality |
Date: | 2014-09-16 12:15:59 |
Message-ID: | CAA-aLv6DwCyb4fH=hBX=wp-9FHwSg80cj2yK4JbYL2e5GmTi_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-09-16 12:17:01 | Re: Sequence Access Method WIP |
Previous Message | Michael Paquier | 2014-09-16 12:11:06 | Re: WAL format and API changes (9.5) |