FUNCION INSERT

-- Ejemplo de creación de una función básica en PostgreSQL CREATE OR REPLACE FUNCTION rrhh.__notas_01__set_award_student( __p_id_main INTEGER, __p_data jsonb) 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; BEGIN IF __id_sede IS NULL THEN RAISE EXCEPTION USING ERRCODE = 'SMILE', MESSAGE = __ERROR_ANP; END IF; --------------------------------- RAISE NOTICE '%', __JSONB_REQUISITOS_PRIMER_NIVEL; -------------- FIN -------------- __result = JSONB_BUILD_OBJECT('status', __STATUS_SUCCESS, 'msj' , 'Ejecución correcta'); WITH ta as (SELECT (t.unw->>'id_award')::INTEGER AS id_award, (t.unw->>'desc_award')::CHARACTER VARYING AS desc_award, (t.unw->>'fecha_award')::TIMESTAMP WITHOUT TIME ZONE AS fecha_regi FROM (select JSONB_ARRAY_ELEMENTS(json_awards) AS unw from notas.notas_estudiante where _id_main=4049 and _id_estudiante = 606 ) AS t) select * from ta select * from notas.notas_estudiante where _id_main=4049 and _id_estudiante = 606 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', JSONB_ARRAY_LENGTH(COALESCE(ne.json_awards,'[]'::JSONB)), 'id_estudiante', 1184, 'id_award' , 2, 'fecha_award' , now(), 'desc_award' , 'es un lider positivo para sus compañeros' ), TRUE )) WHERE _id_main = __p_id_main AND _id_estudiante = 1184; 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$;