Execute a rollback, after running a procedure

Question

I have a problem in creating an unit test to a query. In the Java method, it looks like this:

public String method () {
    StringBuilder query = new StringBuilder();
    query.append(" BEGIN  ");
    query.append(" <stored procedure name> ;");
    query.append(" END; " );
    return query.toString(); }

In test methods, it must do a rollback transaction. For a classic insert / delete / update, they are added in a batch with start transaction / rollback. But I am not sure if it works for stored procedures, especially if they are contained by "begin" / "end". I need to know the same answer for PostgreSQL.

You cannot "rollback" `StringBuilder`. It looks like you have deep misunderstanding of transactional model. — Andremoniy, Dec 15 '15 at 14:37
The method returns the String value of the StringBuilder. And the Statement object is made with this string. — Catalin Vladu, Dec 15 '15 at 14:56

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

For starters, there are no stored procedures in Postgres. Only functions, which do almost, but not quite, the same. A DO statement or a prepared statement might work for you, too.

In particular, functions are always atomic and run inside a transaction. Either within the outer transaction from where they are called, or in a transaction of their own.

And yes, functions can be rolled back just like any other SQL command. Some commands that cannot be rolled back can also not be nested in an outer transaction (like CREATE DATABASE), and therefore cannot be used inside a function either.

BEGIN;
-- do something else
SELECT myfunc();
-- do something else
ROLLBACK;  -- everything back to the start (well, almost everything)

Community

1
1

answered Dec 16 '15 at 11:10

Erwin Brandstetter

605,456
145
1,078
1,228

I need the syntax for Java, to run them as statements, with rollback transaction. One of the problems is if in Oracle, if I put: "begin end; rollback; ", the updates are commited. I want to know if it is possible for unit test to run this as a rollback transaction, without delete "begin" and "end". – Catalin Vladu Dec 17 '15 at 10:33
@CatalinVladu: `ROLLBACK` only makes sense after `BEGIN` to start a transaction. And yes, functions are rolled back like any other SQL command. – Erwin Brandstetter Dec 17 '15 at 21:01
I know the functions / stored procedures can rolled back. The problem is with "begin / end". I put in SQL Developer "begin insert < anything > ; end;" and the transaction was committed. This is the reason for I asked how to put the block containing "begin / end" in a rollback transaction. – Catalin Vladu Dec 18 '15 at 08:33
@CatalinVladu: Not sure what you are asking. There are the key words `BEGIN` and `END` in the body of a plpgsql function. But the function body is a quoted ***string*** and your client should be able to detect that. Maybe your client does not does not understand dollar-quoting? Like in this case? http://stackoverflow.com/q/22747225/939860 – Erwin Brandstetter Dec 18 '15 at 10:35
Update: Postgres supports stored procedures since version 11. – Marko Prcać Dec 29 '20 at 15:43

Execute a rollback, after running a procedure

1 Answers1