CODEWITHSUNDEEP
postgresqlstored-procedures
Alexander Chandra
Alexander Chandra

Reputation: 659

Postgresql how to multiple stored procedure in transaction

I have many stored procedure in my postgresql db, and for some reason i need to run many procedure in transaction so if there is a error it will rollback.

is there any way to do this?

edit 1

i run this through java and for some reason i cant make transaction from java and i cant run query string, just store procedure only. I actually thinking making procedure like this

CREATE OR REPLACE FUNCTION ldt_pricing_rule_v1_api.start()
  RETURNS VOID
  LANGUAGE PLPGSQL
  SECURITY DEFINER
AS $$
BEGIN
  EXECUTE 'begin transaction'
  RETURN;
END
$$;

select ldt_pricing_rule_v1_api.start();

but it's will display this

ERROR: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead.

Upvotes: 0

Views: 2894

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45940

BEGIN ... COMMIT should to work.

BEGIN
  SELECT func1();
  SELECT func2();
COMMIT;

PostgreSQL 11 (it is not released yet) has procedures where you can control transactions explicitly. Procedures are started by CALL statement like any other databases. Now, PostgreSQL functions doesn't allow control transactions (explicitly).

Any PostgreSQL function is executed under transaction - explicitly started by user (like my example), or implicitly started by system (by autocommit mode).

So outer BEGIN starts explicit transaction:

BEGIN
  SELECT func1();
  SELECT func2();
COMMIT;

and if there is any unhandled fail, then only ROLLBACK command is available.

or implicit transaction:

CREATE OR REPLACE FUNCTION outerfx()
RETURNS void AS $$
BEGIN
  PERFORM func1();
  PERFORM func2();
END;
$$ LANGUAGE plpgsql;

SELECT outerfx(); -- starts outer transaction implicitly.

Now, functions func1, func2 are executed under transaction too.

Upvotes: 2

Related Questions