--On 27. September 2009 14:36:45 -0400 Robert Haas <robertmhaas@gmail.com>
wrote:
> On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Well, yeah. That's exactly how it's documented to work: an ON INSERT
>>>> rule is executed after the INSERT proper.
>>
>>> I'm confused. DO INSTEAD doesn't mean DO INSTEAD?
>>
>> It does. What it doesn't mean is "IF ... THEN ... ELSE ...".
>> The OP's rule actually works more like
>>
>> if (!(EXISTS ...))
>> INSERT ...
>>
>> if ((EXISTS ...))
>> UPDATE ...
>
> <reads section 36.3 of the fine manual>
>
> OK, I get it now.
I think the manual is a bit confusing at this point:
"For ON INSERT rules, the original query (if not suppressed by INSTEAD) is
done before any actions added by rules."
I read this like "...if it suppressed, the INSERT in not done..."
But no problem, will try to work around this with a procedure.
>
>>>> You could maybe make this work with a BEFORE INSERT trigger.
>>
>>> I'm not sure you can make it reliable though.
>>
>> Concurrent inserts make things even more interesting, yes; but the rule
>> had no hope of handling that anyway.
>
> OK.
>
> Sometimes when I've needed to do this I've written a PL/pgsql function
> that tries the insert and then fails over to an UPDATE if the INSERT
> fails due to a unique-violation. I'm not sure that's 100% robust
> either, though, unless using serializable mode.
>
> ...Robert
***
www.drbott.info. Dr. Bott KG, D-07426 Oberhain, Germany, HRA Jena 201367
