Postgresql how to multiple stored procedure in transaction

Question

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.

Pavel Stehule · Accepted Answer · 2018-07-24 06:18:34Z

2

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.

edited Jul 24, 2018 at 6:18

answered Jul 24, 2018 at 5:10

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Chandra Over a year ago

thank you for the idea but it's cannot solved my problem. see my edit

Pavel Stehule Over a year ago

@AlexanderChandra - just you cannot do it. This work should be implemented in PostgreSQL differently. PostgreSQL has different transactional controlling model than you can know from Oracle or MSSQL, and some patterns should be done differently. Is not possible move code between these databases in 1:1 style.

Collectives™ on Stack Overflow

Postgresql how to multiple stored procedure in transaction

edit 1

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

edit 1

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related