modelo de funcion

CREATE OR REPLACE FUNCTION plan_inst.__plan_estudio_eliminar_curso( __p_id_course integer, __p_type_course character varying) RETURNS jsonb LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE --CONSTANTES __ERROR_ANP CONSTANT CHARACTER VARYING DEFAULT 'Acción no permitida'; __MSJ_SUCCESS CONSTANT CHARACTER VARYING DEFAULT 'Se eliminó'; __ERROR_CONTROLED CONSTANT INTEGER DEFAULT 400; __ERROR_UNCONTROLED CONSTANT INTEGER DEFAULT 500; __GENERAL_TYPE CONSTANT CHARACTER VARYING DEFAULT 'General'; __EQUIVALENT_TYPE CONSTANT CHARACTER VARYING DEFAULT 'Equivalente'; --VARIABLES __result JSONB; __msj_excep TEXT; __count_exists INTEGER; BEGIN IF __p_id_course IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; IF __p_type_course IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; -------------- FIN -------------- IF __p_type_course = __GENERAL_TYPE THEN DELETE FROM plan_inst.audi_curso_ugel_x_grado WHERE _id_curso_ugel = __p_id_course; DELETE FROM plan_inst.cursos WHERE id_curso = __p_id_course; ELSE DELETE FROM plan_inst.audi_curso_equivalencia WHERE _id_curso_equiv = __p_id_course; DELETE FROM plan_inst.curso_equivalente WHERE id_curso_equiv = __p_id_course; END IF; __result = JSONB_BUILD_OBJECT('msj' , __MSJ_SUCCESS); RETURN __result; EXCEPTION WHEN SQLSTATE 'SMILE' THEN __result = JSONB_BUILD_OBJECT('status', __ERROR_CONTROLED, 'msj' , SQLERRM); RETURN __result; WHEN OTHERS THEN GET STACKED DIAGNOSTICS __msj_excep = PG_EXCEPTION_CONTEXT; __result = JSONB_BUILD_OBJECT('status', __ERROR_UNCONTROLED, 'msj' , 'Hubo un error', 'stack_error', CONCAT(SQLERRM, ' - ', __msj_excep)); RETURN __result; END; $BODY$;

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.