localizing a transaction within a procedure externally

mleko 0
Viewed 17

With regard to my previous post , I am trying to avoid schema prefixes by setting search_paths, however, I want to limit the scope as much as possible so I'm doing this:

BEGIN TRANSACTION;
  SET LOCAL search_path to mySchema;
  CALL STORED_PROCEDURE();
  UPDATE TABLE MYTABLE1(//SOME CODE);
COMMIT;

CALL STORED_PROCEDURE();

Now, the proc has it's own begin, commit statements, so I'm wondering if the procedure will respect it's outer transaction, i.e.

  1. Will the stored_procedure respect the search_path set by it's parent transaction block?
  2. if updating the table fails, will operations done inside the procedure also rollback?
transactions postgresql
1 Answers

The BEGIN and END in a PL/pgSQL procedure has no connection with the transaction commands BEGIN and COMMIT/END. They just mark a block.

The procedure will use your search_path, unless you override that inside your procedure. A COMMIT inside the procedure could require special consideration, but that is not a problem here, since you are not allowed to COMMIT inside a procedure that is called from a multi-statement transaction anyway.

Laurenz Albe0
Related