Re: Insert with query

On 05/31/2013 08:15 AM, Kevin Grittner wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>> On 05/31/2013 06:32 AM, Kevin Grittner wrote:

>>
>> But why? The OP specified FOR EACH ROW in the trigger statement.
>
> Hmm. I went to the SQL spec, and the behavior expected by Juliano
> seems to be what it mandated by the spec. To quote a couple brief
> snippets from the spec, the trigger should fire "either immediately
> before the triggering event or immediately after it, according to
> its specified trigger action time" and "Every trigger event arises
> as a consequence of executing some SQL-data change statement." It
> is clear from the spec that a MERGE *statement*, for example, can
> cause different types of triggers (INSERT and UPDATE, for example)
> to fire. The distinction between a "triggering event" and the
> "data change statement" causing the event is pretty explicit.
>
> At least, that's how I interpret the spec.
>
> That said, it would be a big behavior change to make it work that
> way. Such a change could only be considered for a major release,
> and the benefits of conforming to the standard in this respect
> would need to be balanced against the cost of making the change and
> the inevitable breakage of currently-working applications which
> would certainly result.

The thing is I thought it was working to spec and the docs would seem to
be saying it does:

http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html
"
FOR EACH ROW
FOR EACH STATEMENT
This specifies whether the trigger procedure should be fired once for
every row affected by the trigger event, or just once per SQL statement.
If neither is specified, FOR EACH STATEMENT is the default. Constraint
triggers can only be specified FOR EACH ROW."

Now it is entirely possible I am reading the above wrong and if that is
the case I would welcome an explanation of where I am misinterpreting it.

>
> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com