> And what if I didn't want the commit to fail? What if I half expected
> the insert
> to fail and then want to do an update instead? That's a pretty common
> pattern - try
> to insert, if fail - do an update instead.
>
> Is this behaviour some kind of standard? Can it be changed?
Not in this life :)
PostgreSQL does not support nested transactions. If transaction fails, the
transaction fails.
You may write a trigger instead of (or combined with) UNIQUE CHECK, like
this:
CREATE FUNCTION fn_foo_insupd() RETURNS OPAQUE AS '
DECLARE
chk INT4;
BEGIN
SELECT id INTO chk FROM foo WHERE id = NEW.id;
IF FOUND THEN
PERFORM some_action(NEW.id);
RETURN NULL;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER trg_foo_insupd BEFORE INSERT OR UPDATE ON foo FOR EACH ROW
EXECUTE PROCEDURE fn_foo_ins();
Note that if you insert a row into a keyed table that has a trigger, you
cannot delete this row or update the key in the same transaction.
Alex Bolenok.
