How to implement SAVE TRAN of MSSQL in PostgreSQL?

Question

Looking forward for a equivalent code in PostgreSQL, especially how to implement SAVE TRAN, @@trancount, and @@error.

The below code is in MSSQL and has to be migrated:

CREATE PROCEDURE [APPLY_TRANSCO_CASH]
  AS
 BEGIN
DECLARE 
    @trancount int,
    @error int,
    @rowcount int,
    @ret int
 SELECT @trancount = @@trancount
IF(@trancount <> 0)
BEGIN   
    SAVE TRAN tran_APPLY_TRANSC
END
ELSE
BEGIN
    BEGIN TRAN tran_APPLY_TRANSC
END

UPDATE ELIOT_TMp
SET Country = 'v'
SELECT @error = @@error, @rowcount = @@rowcount
IF (@error <> 0)
BEGIN 
    SELECT @ret = -1
    GOTO Error
END
SELECT @ret = @rowcount
GOTO Normal_End

Normal_End:
    IF (@trancount=0)
    BEGIN
        COMMIT TRANSACTION
    END
    RETURN @ret
Error:

    ROLLBACK TRANSACTION tran_APPLY_TRANSC
    RETURN @ret

Answer 1

Assuming save tran establishes a savepoint, I think the equivalent would be:

create procedure apply_transco_cash(inout retcode int)
AS
$$
begin

  UPDATE eliot_tmp
    SET Country = 'v';

  -- this returns the number of affected rows
  GET DIAGNOSTICS recode = ROW_COUNT;

  commit;
    
exception 
   when others then 
     retcode := -1;
end;
$$
language plpgsql;

To deal with errors, you use an exception block in PL/pgSQL which automatically establish an implicit savepoint (in this case "covering" the whole procedure starting with the first begin). Explicitly setting a savepoint is not necessary (and you can't rollback to a savepoint in PL/pgSQL)

GET DIAGNOSTICS is used to retrieve the number of rows affected by the last statement.