BEGIN TRANSACTION Statement - SQL Server to PostgreSQL Migration

In SQL Server and PostgreSQL, the BEGIN TRANSACTION statement starts a transaction.

Note that PostgreSQL does not allow using BEGIN TRANSACTION in a stored procedure, although you can use the COMMIT and ROLLBACK statements in procedures.

SQL Server and PostgreSQL:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  BEGIN TRANSACTION;
 
  -- Insert a sample row
  INSERT INTO colors VALUES('White');
 
  COMMIT;

SQL Server allows you to start and end transactions in stored procedures:

SQL Server:

   -- Sample procedure
  CREATE PROCEDURE sp_add_color @name VARCHAR(30), @type CHAR(1)
  AS
    BEGIN TRANSACTION;
 
    -- Insert a row
    INSERT INTO colors VALUES(@name);
 
    IF @type = 'C' 
      COMMIT;
    ELSE
      ROLLBACK;
  GO      
 
  -- Call the procedure
  EXEC sp_add_color 'White', 'R' 
  EXEC sp_add_color 'Blue', 'C'
 
  -- Only one row returned
  SELECT * FROM colors;
  /* Blue */

In a PostgreSQL stored procedure, you can only commit or rollback the current transaction. PostgreSQL does not allow you to start a transaction in stored procedures.

But a new transaction immediately starts after the COMMIT and ROLLBACK statement issued in procedure.

PostgreSQL:

  -- Sample procedure
  CREATE OR REPLACE PROCEDURE sp_add_color(p_name VARCHAR(30), p_type CHAR(1))
  AS $$
  BEGIN 
    -- Insert a row
    INSERT INTO colors VALUES(p_name);
 
    IF p_type = 'C' THEN 
      COMMIT; 
    ELSE
      ROLLBACK;
    END IF;
  END;
  $$ LANGUAGE plpgsql;      
 
  -- Call the procedure
  CALL sp_add_color('White', 'R'); 
  CALL sp_add_color('Blue', 'C');
 
  -- Only one row returned
  SELECT * FROM colors;
  /* Blue */

For more information, see SQL Server to PostgreSQL Migration.