What's the equivalent of Sybase BEGIN ATOMIC in PostgreSQL?
It should be something like:
CREATE OR REPLACE FUNCTION my_func()
RETURNS int
AS $$
BEGIN
BEGIN ATOMIC
UPDATE stetment1;
UPDATE stetment2;
UPDATE stetment3;
END;
END;
$$ LANGUAGE plpgsql;
The three update statements must either succeed or fail all together as one.
the three update statements must either succeed or fail all together as one..
The comments below the question are a misunderstanding. Every Postgres function is transactional and always either succeeds or fails alltogether as one. You'd only need a PROCEDURE for the opposite: to commit what's already done inside the body of a procedure.
The literal clause BEGIN ATOMIC is used for the new standard-SQL CREATE FUNCTION syntax, which has eventually been added with Postgres 14. But it's just as "atomic" as any other Postgres function.
Either way, with new syntax you could write:
CREATE OR REPLACE FUNCTION my_func()
LANGUAGE plpgsql
RETURNS void -- since you are not returning anything
BEGIN ATOMIC
update stetment1;
update stetment2;
update stetment3:
END;
See:
You can use BEGIN ATOMIC; ... END; statement in PostgreSQL as shown below. *BEGIN ATOMIC; ... END; statement works only for LANGUAGE SQL according to the doc and my answer gives some examples with BEGIN ATOMIC; ... END; statement:
CREATE FUNCTION my_func() RETURNS INTEGER LANGUAGE SQL
BEGIN ATOMIC; -- Here
SELECT 2 + 3;
END; -- Here
And actually, not using BEGIN ATOMIC; ... END; statement as shown below is same as above because in PostgreSQL, BEGIN ATOMIC; ... END; statement has nothing special, it is just a syntax and actually in PostgreSQL, a function is automatically run in a transaction to rollback if there is error:
CREATE FUNCTION my_func(value INTEGER) RETURNS INTEGER AS $$
SELECT 2 + 3;
$$ LANGUAGE SQL;
