CREATE OR REPLACE FUNCTION function_base_con_tablas_temporales(
__p_id_aula integer)
RETURNS jsonb
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
--CONSTANTES
__MSJ_ERROR CONSTANT CHARACTER VARYING DEFAULT 'Hubo un error';
__FLG_ACTI CONSTANT CHARACTER VARYING DEFAULT '1';
__GRUPO_PARENTESCO CONSTANT INTEGER DEFAULT 5;
--VARIABLES
__result JSONB;
__msj_excep TEXT;
__list_fam JSONB;
__desc_aula CHARACTER VARYING;
BEGIN
SELECT CONCAT(COALESCE(a.desc_aula, a.nombre_letra), ' - ', g.abvr,' ',n.abvr, ' - ', s.desc_sede)
INTO __desc_aula
FROM aula a
INNER JOIN grado g
ON g.nid_grado = a.nid_grado
INNER JOIN nivel n
ON n.nid_nivel = g.id_nivel
INNER JOIN sede s
ON s.nid_sede = a.nid_sede
WHERE a.nid_aula = __p_id_aula;
CREATE TEMP TABLE temp_students AS
SELECT __id_persona AS id_persona,
cod_familia
FROM persona_x_aula pxa
INNER JOIN sima.detalle_alumno da
ON da.nid_persona = pxa.__id_persona
WHERE pxa.__id_aula = __p_id_aula
AND pxa.flg_acti = __FLG_ACTI;
CREATE TEMP TABLE temp_families AS
SELECT f.id_familiar,
(__nombre_corto(f.id_familiar, '2','APEPATE-APEMATE-,-NOMBABVR')) AS nom_familiar,
f.usuario,
c.desc_combo AS parentesco,
fxf.flg_usuario,
(__nombre_corto(s.id_persona, '1', 'APEPATE-APEMATE-,-NOMBABVR')) AS nom_estudiante,
TO_CHAR(fec_hora_ult_cambio_clave, 'dd/mm/yyyy hh:mi am') AS fec_hora_ult_cambio_clave,
f.email1 AS correo
FROM sima.familiar_x_familia fxf
INNER JOIN temp_students s
ON s.cod_familia = fxf.cod_familiar
INNER JOIN familiar f
ON f.id_familiar = fxf.id_familiar
AND f.flg_acti = __FLG_ACTI
AND usuario IS NOT NULL
INNER JOIN combo_tipo c
ON c.grupo = __GRUPO_PARENTESCO
AND c.valor = fxf.parentesco
WHERE (fxf.flg_usuario IS NULL OR fxf.flg_usuario = __FLG_ACTI);
WITH family_and_session AS (
SELECT tf.*,
lu.metadata
FROM temp_families tf
LEFT JOIN logs_usuario lu
ON lu.id_usuario = tf.id_familiar
AND lu.tipo_usuario = 'FAMI'
ORDER BY tf.nom_estudiante, tf.nom_familiar
)
SELECT ARRAY_TO_JSON(ARRAY_AGG(tab))::JSONB
INTO __list_fam
FROM family_and_session tab;
__result := JSONB_BUILD_OBJECT(
'status', 0,
'data' , JSONB_BUILD_OBJECT(
'familiares', COALESCE(__list_fam, '[]'::JSONB),
'desc_aula' , __desc_aula
)
);
DROP TABLE temp_students;
DROP TABLE temp_families;
RETURN __result;
EXCEPTION
WHEN SQLSTATE 'SMILE' THEN
__result = JSONB_BUILD_OBJECT('status', 1, 'msj' , SQLERRM);
RETURN __result;
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS __msj_excep = PG_EXCEPTION_CONTEXT;
__result = JSONB_BUILD_OBJECT('status', 2 , '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.