I currently have an obsolete postgresql code.
I do not have much experience with functions or triggers, I only ask for help to get possible obsolete commands or functions since it is a somewhat old code and I am working with a server in postgresql 9.6. Below the code, any help is well received!
-- FUNCTION: public.ex_estadistica_devueltos_2010()
-- DROP FUNCTION public.ex_estadistica_devueltos_2010();
CREATE FUNCTION public.ex_estadistica_devueltos_2010()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
users_rec RECORD;
prefix_tblname text;
userrec RECORD;
query text;
rs RECORD;
rsd RECORD;
_admin_id bigint;
_campana_id bigint;
_usuario_id bigint;
_smtp_hard_code text;
_smtp_hard_code_detail text;
_smtp_code_bounce text;
_email text;
_date TEXT;
_date_arr text[];
_lista_id bigint;
_status boolean;
BEGIN
_admin_id = NEW.id_admin;
_campana_id = NEW.id_campana;
_usuario_id = NEW.id_usuario;
_smtp_hard_code = NEW.smtpprefix;
_smtp_hard_code_detail = NEW.smtpprefixdetail;
_smtp_code_bounce = NEW.smtpcode1;
_email = NEW.email;
_date := CURRENT_TIMESTAMP;
_lista_id = NEW.id_lista;
_status = NEW.status;
IF _email IS NOT NULL THEN
_date_arr := string_to_array(_date, '.');
IF _usuario_id IS NOT NULL AND _admin_id IS NOT NULL AND _campana_id IS NOT NULL THEN
SELECT INTO users_rec usuario FROM ex_admin WHERE id_admin = _admin_id AND id_modulo = '3';
IF FOUND THEN
IF users_rec.usuario IS NOT NULL THEN
prefix_tblname:='ex_tbl_' || regexp_replace(quote_ident(users_rec.usuario), '"', '', 'g');
query := 'SELECT id_usuario FROM ' || prefix_tblname || ' WHERE id_usuario = ' || _usuario_id;
EXECUTE query;
IF FOUND THEN
-- IF _smtp_hard_code = '550' THEN
IF _status = 't' THEN
-- IF _smtp_hard_code_detail !~ E'^SMTP;\s550\sSC\-[0-9]{3}\(\.*\)$' THEN
IF _smtp_code_bounce ~* E'^550\s\d\.\d\.\d$' THEN
EXECUTE 'UPDATE ' || quote_ident(prefix_tblname) || ' SET status = ''3'', fecha_out = ''' || _date_arr[1] || ''' WHERE id_usuario = ' || _usuario_id;
SELECT INTO rs email FROM ex_tbl_blacklist_email WHERE id_admin = _admin_id AND email = _email;
IF FOUND THEN
UPDATE ex_tbl_blacklist_email SET count = count + 1 WHERE id_admin = _admin_id AND email = _email;
ELSE
INSERT INTO ex_tbl_blacklist_email (id_usuario, id_admin, email, tipo, count) VALUES (_usuario_id, _admin_id, _email, 2, 1);
END IF;
UPDATE ex_tbl_campanas SET outnet = outnet + 1, devueltos = devueltos + 1, recibidos = recibidos - 1 WHERE id_campana = _campana_id AND id_admin = _admin_id;
END IF;
ELSE
EXECUTE 'UPDATE ' || quote_ident(prefix_tblname) || ' SET status = ''2'' WHERE id_usuario = ' || _usuario_id;
INSERT INTO ex_sys_cuarentena ( id_usuario, id_admin, id_campana, id_lista ) VALUES ( _usuario_id, _admin_id, _campana_id, _lista_id );
UPDATE ex_tbl_campanas SET devueltos = devueltos + 1, recibidos = recibidos - 1 WHERE id_campana = _campana_id AND id_admin = _admin_id;
END IF;
--UPDATE ex_tbl_campanas SET devueltos = devueltos + 1 WHERE id_campana = _campana_id AND id_admin = _admin_id;
END IF;
END IF;
END IF;
END IF;
END IF;
RETURN NULL;
END;
$BODY$;
ALTER FUNCTION public.ex_estadistica_devueltos_2010()
OWNER TO postgres;
COMMENT ON FUNCTION public.ex_estadistica_devueltos_2010()
IS 'Contador de devueltos por campaña';