What can be done to simulate a MS SQL try catch but in PostgreSQL?

Question

What can be done to simulate a MS SQL try catch but in PostgreSQL?

Navigation

#1 by (2 votes)

2

What can be done to simulate a Microsoft SQL Server try catch but in PostgreSQL?

BEGIN TRY
    BEGIN TRANSACTION
        IF EXISTS (SELECT * FROM USUARIOS WHERE ID_USUARIO = @ID_RESPONSABLE)
            BEGIN
                DECLARE @ID_CLIENTE INT
                SET @ID_CLIENTE = (SELECT ID_MEMBRESIA FROM CLIENTES WHERE
                @NOMBRE = NOMBRE AND
                @APELLIDO_P = APELLIDO_P AND
                @APELLIDO_M = APELLIDO_M AND
                @FECHA_NAC = FECHA_NAC
                )

                IF @ID_CLIENTE IS NULL
                    BEGIN
                        INSERT INTO CLIENTES
                        VALUES (@ID_MEMBRESIA,@NOMBRE,@APELLIDO_P,@APELLIDO_M,
                        @FECHA_NAC,@CALLE,@NUMERO,@COLONIA,@CODIGO_P,@MUNICIPIO,@LOCALIDAD,@ESTADO,'SI')

                        INSERT INTO BITACORA VALUES 
                        (@ID_RESPONSABLE, 'CLIENTES',('Dio de alta un nuevo cliente: '+
                        @NOMBRE +' '+@APELLIDO_P+' '+@APELLIDO_M),
                        GETDATE())

                        SET @STATUS = 'true'
                        SET @MENSAJE = 'Se ha registrado un nuevo cliente satisfactoriamente'
                    END
                ELSE
                    BEGIN
                        SET @MENSAJE = 'Este cliente ya existe en la base de datos'
                        PRINT @MENSAJE
                        SET @STATUS ='false'
                    END
            END
        ELSE
            BEGIN 
                SET @MENSAJE = 'Usuario denegado. Operaciòn inclompleta'
                PRINT @MENSAJE
                SET @STATUS ='false'
            END
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SET @STATUS = 'false'
    ROLLBACK TRANSACTION
END CATCH

sql sql-server postgresql

asked by Alan Rodriguez 20.04.2018 в 23:43

source

1 answer

dates timestamp problems when converting How to delete characters in python console? [closed]

score 2 · Answer 1

The syntax for trapping exceptions in PostgreSQL is an extension of the normal syntax used for a BEGIN block with a EXCEPTION clause:

Assuming we have the registry in users:

| id | nombres | apellidos |
|----|---------|-----------|
| 1  | Alan    | Rodriguez |

We execute:

BEGIN
    UPDATE usuarios SET nombres = 'Joe' WHERE apellidos = 'Rodriguez';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_por_cero THEN
        RAISE NOTICE 'Error: ¡División por cero!';
        RETURN x;
END;

By default, any error that occurs in a PostgreSQL function overrides the execution of the function and also the surrounding transaction.

When the control reaches the allocation of y , it will fail with an error of division_por_cero . This will be captured by the EXCEPTION clause. The value returned in the declaration RETURN will be the incremented value of x, but the effects of the UPDATE command will have been reverted.

So the final result is that in the database you have registered Alan Rodriguez and not Joe Rodriguez .

You can read more about this in the official PostgreSQL documentation