From: | "Alex Bolenok" <abolen(at)chat(dot)ru> |
---|---|
To: | "Chris Bitmead" <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ROLLBACK automatically |
Date: | 2000-07-25 07:15:50 |
Message-ID: | 003701bff608$36eee3a0$df02a8c0@artey.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Heaven | 2000-07-25 07:47:57 | Re: Statistical Analysis |
Previous Message | Tom Lane | 2000-07-25 07:14:56 | Re: function language type? |