Postgres Pro
Downloads
Home   >   mailing lists

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

From Thom Brown
Subject Re: Triggers with DO functionality
Date
Msg-id CAA-aLv6DwCyb4fH=hBX=wp-9FHwSg80cj2yK4JbYL2e5GmTi_g@mail.gmail.com
Whole thread Raw
In response to Re: Triggers with DO functionality  (Jaime Casanova <jaime@2ndquadrant.com>)
Responses Re: Triggers with DO functionality
List pgsql-hackers
Tree view
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 } ]
    }

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

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: CRC algorithm (was Re: [REVIEW] Re: Compression of full-page-writes)
Next
From: Robert Haas
Date:
Subject: Re: Support for N synchronous standby servers