On Sun, Sep 27, 2009 at 11:36 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Sat, Sep 26, 2009 at 12:35 PM, Tom Lane <t...@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. >>> 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs