FUNCION PARA TRAER ESTUDIANTES CON AWARDS

CREATE OR REPLACE FUNCTION notas.__calificaciones_36__get_data_students_with_awards( __p_id_main 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; __total_awards_posit INTEGER; __total_awards_neg INTEGER; __total_awards_total INTEGER; BEGIN IF __id_sede IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; __result = JSONB_BUILD_OBJECT('status', __STATUS_SUCCESS, 'msj' , 'Ejecución correcta'); --------------------------------- WITH tabla_nueva AS ( WITH tabla_estudents AS ( WITH aw AS (SELECT (t.unw->>'id_award')::INTEGER AS id_award, (t.unw->>'fecha_award')::DATE::TEXT AS fecha_award, (t._id_estudiante)::INTEGER AS _id_estudiante FROM (SELECT _id_estudiante,JSONB_ARRAY_ELEMENTS(json_awards) AS unw FROM notas.notas_estudiante WHERE _id_main=7365) AS t) SELECT aw.id_award AS id_award, aw._id_estudiante AS _id_estudiante_main, aw.fecha_award::DATE AS fecha_award, na.award_points AS award_points, nc.* FROM aw, notas.award na, notas.__calificaciones_get_estudiante_main__11(7365) nc WHERE aw._id_estudiante = nc._id_estudiante AND aw.id_award = na.id_award ) -- SELECT COUNT( award_points ) FILTER (WHERE award_points = 1), -- COUNT( award_points ) FILTER (WHERE award_points = -1), -- COUNT( award_points ) AS tot -- INTO __total_awards_posit, -- __total_awards_neg, -- __total_awards_total -- FROM abla_estudents --select * from tabla_estudents group by _id_estudiante SELECT _id_estudiante, ARRAY_TO_JSON(ARRAY_AGG(JSONB_BUILD_OBJECT('id_award',id_award, 'fecha_award',fecha_award, 'award_points',award_points ) ) ) AS awards FROM tabla_estudents GROUP BY _id_estudiante) select nc.*, tn.awards from --notas.award, notas.__calificaciones_get_estudiante_main__11(7365) nc LEFT JOIN tabla_nueva tn ON (nc._id_estudiante = tn._id_estudiante) -------------- FIN -------------- 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$;

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.