0

Hi I have more used with SQL procedure. Just trying to achieve below scenarios.

In Postgres Stored Procedure,

  1. do we need to commit if there are multiple insert statements OR By default Postgressql handle commit and no need to mention (if we don't have anything to handle on Catch OR Rollback) ?

  2. How to write , Try/Catch Or Rollback from Stored Procedure ?

    CREATE OR REPLACE PROCEDURE "app"."sproc_data_create"
( name varchar(100))
LANGUAGE plpgsql
AS
$$
DECLARE idIdentity int;
 BEGIN

 INSERT INTO data(Name) VALUES (name) RETURNING Id into idIdentity;
 INSERT INTO relationalData(dataId) VALUES (idIdentity);

// COMMIT , Do we need commit statement  
// HOW to handle if Rollback or Exception ?
 SELECT idIdentity as Id;   /
 END

Can someone please guide on this.

dsi
  • 3,199
  • 12
  • 59
  • 102
  • 1
    Too many questions in one. Start by reading the PL/pgSQL documentation. – Laurenz Albe Apr 13 '22 at 14:46
  • [Transaction Management](https://www.postgresql.org/docs/current/plpgsql-transactions.html) [Trapping Errors](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING) –  Apr 13 '22 at 14:48
  • Is "Commit" Required OR it is by default ? I couldn't find this under documentation but observed, examples without "commit" even though multiple insert statements. – dsi Apr 13 '22 at 14:54
  • 1
    A function/procedure runs in it's own transaction so there is an implicit commit on completion. – Adrian Klaver Apr 13 '22 at 15:17
  • Ok. Is it still remain when Procedure internally calling "Call " – dsi Apr 13 '22 at 15:20

0 Answers0