Example Function Postgresql

-- Ejemplo de creación de una función básica en PostgreSQL CREATE OR REPLACE FUNCTION rrhh.__configuracion__alert_day_medico__09( __id_sede 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_ERROR CONSTANT INTEGER DEFAULT 1; STATUS_SUCCESS CONSTANT INTEGER DEFAULT 0; --VARIABLES __result JSONB; __msj_excep TEXT; BEGIN IF __id_sede IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = ERROR_ANP; END IF; --------------------------------- RAISE NOTICE '%', __JSONB_REQUISITOS_PRIMER_NIVEL; -------------- FIN -------------- __result = JSONB_BUILD_OBJECT('status', STATUS_SUCCESS, 'msj' , 'Ejecución correcta'); 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_ERROR, 'msj' , 'Hubo un error', 'stack_error', CONCAT(SQLERRM, ' - ', __msj_excep)); RETURN __result; END; $BODY$;