funcion para schedule

CREATE OR REPLACE FUNCTION notas.__tutoria_08__get_student_info_schedule( __p_id_aula integer, __p_fecha date) RETURNS jsonb LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE --CONSTANTES __ERROR_ANP CONSTANT CHARACTER VARYING DEFAULT 'Acción no permitida'; __ERROR_NEE CONSTANT CHARACTER VARYING DEFAULT 'No existen estudiantes en este curso'; __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 __p_id_aula IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_NEE; END IF; IF __p_fecha IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; --------------------------------- SELECT pa.__id_persona, pa.__id_aula, sd.firma_agenda, sd.json_comments, pers.nom_persona AS nombres_estudiante, INITCAP(SPLIT_PART(pers.nom_persona , ' ', 1)) AS primer_nombre, INITCAP(CONCAT(pers.ape_pate_pers, ' ', SUBSTRING(pers.ape_mate_pers, 1, 1), '.')) AS apellidos_abrev, pers.ape_pate_pers AS apellido_paterno, pers.ape_mate_pers AS apellido_materno, INITCAP(CONCAT(SPLIT_PART(pers.nom_persona , ' ', 1), ' ', pers.ape_pate_pers, ' ', SUBSTRING(pers.ape_mate_pers, 1, 1), '.')) AS nombre_abreviado INITCAP(CONCAT(pers.nom_persona, ' ', pers.ape_pate_pers, ' ', pers.ape_mate_pers)) AS nom_comp_filtro, pers.foto_persona AS profile_image, FROM persona_x_aula pa, sima.detalle_alumno sd, persona pers WHERE pa.__id_aula = 664 AND sd.nid_persona = pa.__id_persona AND pers.nid_persona = pa.__id_persona 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$; ALTER FUNCTION notas.__calificaciones_36__get_student_with_awards(integer, date, date) OWNER TO postgres;