Example Function Postgresql

-- Ejemplo de creación de una función básica en PostgreSQL CREATE OR REPLACE FUNCTION schema.__sistper_01__nombre_funcion( __mi_variable INTEGER) RETURNS jsonb LANGUAGE 'plpgsql' VOLATILE AS $BODY$ DECLARE --CONSTANTES __ERROR_ANP CONSTANT CHARACTER VARYING DEFAULT 'Acción no permitida'; __MSJ_ERROR CONSTANT CHARACTER VARYING DEFAULT 'Hubo un error'; __MSJ_SUCCESS CONSTANT CHARACTER VARYING DEFAULT 'Se actualizó'; __STATUS_500 CONSTANT INTEGER DEFAULT 500; __STATUS_ERROR CONSTANT INTEGER DEFAULT 400; __STATUS_SUCCESS CONSTANT INTEGER DEFAULT 200; --VARIABLES __result JSONB; __msj_excep TEXT; BEGIN IF __mi_variable IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; --------------------------------- RAISE NOTICE 'mi variable es => %', __mi_variable; -------------- FIN -------------- __result = JSONB_BUILD_OBJECT('status', __STATUS_SUCCESS, 'msj' , __MSJ_SUCCESS); RETURN __result; EXCEPTION WHEN SQLSTATE 'SMILE' THEN __result = JSONB_BUILD_OBJECT('status', __STATUS_ERROR, 'msj' , SQLERRM); RETURN __result; WHEN OTHERS THEN GET STACKED DIAGNOSTICS __msj_excep = PG_EXCEPTION_CONTEXT; __result = JSONB_BUILD_OBJECT('status', __STATUS_500 , 'msj' , 'Hubo un error', 'stack_error', CONCAT(SQLERRM, ' - ', __msj_excep)); RETURN __result; END; $BODY$;