Re: Conditionnal validation for transaction

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