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.