Florent THOMAS wrote: >>> 1 - Is there a way to have conditions for committing transactions like in oracle : >>> http://www.scribd.com/doc/42831667/47/Validation-conditionnelle-de-trans action-62 >> >> PostgreSQL follows the SQL standard which does not allow anything like that. >> >> Later versions do allow anonymous blocks, also known as DO statements >> that allow you to execute some code to allow decision making like >> that. So the Oracle example is very similar code in PostgreSQL, except >> that you can't issue ROLLBACK and COMMIT. > > Thanks, Could you precise the sentence bellow > > >> But then you don't need to >> because you can do a conditional error or drop through to a commit. > > How do you do that?
I don't know what exactly Simon meant here, but I'd do it like that in PostgreSQL (example from your link): CREATE TABLE transtest(x smallint); INSERT INTO transtest VALUES (1), (2); CREATE FUNCTION dec_trans() RETURNS void LANGUAGE plpgsql AS $$DECLARE minx transtest.x%TYPE; BEGIN UPDATE transtest SET x=x-1; SELECT min(x) INTO minx FROM transtest; IF minx<0 THEN RAISE EXCEPTION 'bad decrement'; END IF; END$$; SELECT * FROM transtest; x --- 1 2 (2 rows) DO LANGUAGE plpgsql $$BEGIN PERFORM dec_trans(); EXCEPTION WHEN OTHERS THEN NULL; END$$; SELECT * FROM transtest; x --- 0 1 (2 rows) DO LANGUAGE plpgsql $$BEGIN PERFORM dec_trans(); EXCEPTION WHEN OTHERS THEN NULL; END$$; SELECT * FROM transtest; x --- 0 1 (2 rows) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general