XACT_STATE Function - SQL Server to PostgreSQL Migration

In SQL Server, the XACT_STATE function indicates if a transaction is running. It is typically used to check if the procedure or batch can execute a ROLLBACK or COMMIT statements.

In PostgreSQL, you can use the PG_CURRENT_XACT_ID_IF_ASSIGNED function but note that the return valus are different and you may need to change the conditional logic.

SQL Server:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  BEGIN TRANSACTION;
 
  -- Insert a sample row
  INSERT INTO colors VALUES('Black');
 
  PRINT XACT_STATE();
  /* Result: 1 */
 
  IF XACT_STATE() <> 0
    ROLLBACK

PostgreSQL:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  -- Anonymous block starts own transaction  
  DO $$
  BEGIN  
    -- Insert a sample row
    INSERT INTO colors VALUES('Black');
 
    RAISE NOTICE '%', PG_CURRENT_XACT_ID_IF_ASSIGNED();
    /* Result: NOTICE:  1899 */
 
    IF PG_CURRENT_XACT_ID_IF_ASSIGNED() IS NOT NULL THEN
      ROLLBACK;
    END IF;
 
  END $$;

For more information, see SQL Server to PostgreSQL Migration.