set award student

CREATE OR REPLACE FUNCTION notas.__calificaciones_34__set_award_student( __p_id_main integer, __p_data jsonb, __p_fecha date) RETURNS jsonb LANGUAGE 'plpgsql' COST 100 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ó'; __ERROR_EA CONSTANT CHARACTER VARYING DEFAULT 'Se ingreso un array vacio'; __STATUS_ERROR CONSTANT INTEGER DEFAULT 1; __STATUS_SUCCESS CONSTANT INTEGER DEFAULT 0; --VARIABLES __result JSONB; __msj_excep TEXT; __v_id_estu INTEGER; __v_desc_award CHARACTER VARYING(100); __v_id_award INTEGER; __len_awards INTEGER; BEGIN IF __p_id_main IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; IF __p_data = '[]' THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_EA; END IF; --------------------------------- __result = JSONB_BUILD_OBJECT('status', __STATUS_SUCCESS, 'msj' , __MSJ_SUCCESS); FOR __v_id_estu, __v_desc_award, __v_id_award IN SELECT unw->>'id_estudiante', unw->>'desc_award', unw->>'id_award' FROM JSONB_ARRAY_ELEMENTS(__p_data) AS unw LOOP -- Si la descripcion esta vacia que no la guarde IF __v_desc_award IS NOT NULL THEN UPDATE notas.notas_estudiante ne SET json_awards = (SELECT JSONB_SET(COALESCE(ne.json_awards,'[]'::JSONB), ARRAY[JSONB_ARRAY_LENGTH(COALESCE(ne.json_awards,('[]')::JSONB))::TEXT], JSONB_BUILD_OBJECT('idx' , gen_random_uuid(), 'id_award' , __v_id_award, 'fecha_award' , CONCAT(to_char(__p_fecha::DATE, 'dd/mm/yyyy'), ' ', to_char(current_timestamp, 'hh12:mi am')), 'desc_award' , __v_desc_award ), TRUE )) WHERE _id_main = __p_id_main AND _id_estudiante = __v_id_estu; END IF; END LOOP; 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$;