SET client_min_messages = notice;
DO $$
DECLARE
_msj TEXT;
BEGIN
CREATE TEMP TABLE temp_estu AS
WITH estu AS (
SELECT COUNT(he._id_persona) AS count_regi,
da.nid_persona,
da.id_sede_base
FROM sima.detalle_alumno da
LEFT JOIN sima.historico_estudiante he
ON he._id_persona = da.nid_persona
AND he.flg_acti = '1'
GROUP BY he._id_persona, da.nid_persona, da.id_sede_base
)
SELECT *
FROM estu
WHERE count_regi = 1 AND id_sede_base IS NULL;
WITH filter_estu AS (
SELECT he.year,he._id_sede,he._id_grado
FROM temp_estu te
INNER JOIN sima.historico_estudiante he
ON he._id_persona = te.nid_persona
AND he.year = 2020
)
UPDATE sima.detalle_alumno
SET year_base = fi.year,
id_sede_base = fi._id_sede,
id_grado_base = fi._id_grado
FROM filter_estu fi;
DROP TABLE temp_estu;
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS _msj = PG_EXCEPTION_CONTEXT;
RAISE NOTICE 'SE JODIO: % %', SQLERRM, SQLSTATE;
END
$$
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.