Блог

Главная - Вопросы по программированию - недопустимый синтаксис ввода для целого числа: «0A000» КОНТЕКСТ: обновление триггера функции PL/pgSQL

недопустимый синтаксис ввода для целого числа: «0A000» КОНТЕКСТ: обновление триггера функции PL/pgSQL

#postgresql #plpgsql

Вопрос:

У меня есть триггеры функций обновления и вставки, bvtrigger_fct_tr_people_upd и bvtrigger_fct_tr_people_ins

Когда я обновил информацию в главной таблице, я столкнулся с ошибкой

ОШИБКА: неверный синтаксис ввода целого числа: «0A000»

КОНТЕКСТ: функция PL/pgSQL bvtrigger_fct_tr_people_upd() строка 75 при назначении Состояние SQL: 22P02

вот мои таблицы:

 CREATE TABLE pkihtts.bv_tb_people
(
    peopleid integer NOT NULL DEFAULT nextval('pkihtts.bvsq_peopleid'),
    tokenid integer,
    auid character varying(25) COLLATE pg_catalog."default" NOT NULL,
    upn character varying(256) COLLATE pg_catalog."default" NOT NULL,
    firstname character varying(64) COLLATE pg_catalog."default",
    middlename character varying(64) COLLATE pg_catalog."default",
    lastname character varying(64) COLLATE pg_catalog."default" NOT NULL,
    genqual character varying(4) COLLATE pg_catalog."default",
    serialnumber character varying(25) COLLATE pg_catalog."default",
    agency character varying(50) COLLATE pg_catalog."default",
    subagency character varying(64) COLLATE pg_catalog."default",
    affiliation character varying(10) COLLATE pg_catalog."default",
    subscribertype character varying(64) COLLATE pg_catalog."default",
    countryofcitizenship character varying(2) COLLATE pg_catalog."default",
    emailaddress character varying(256) COLLATE pg_catalog."default" NOT NULL,
    dateregistered timestamp without time zone NOT NULL,
    comments character varying(255) COLLATE pg_catalog."default",
    isdeleted character varying(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'n'::character varying,
    modifiedby double precision NOT NULL,
    modifieddate timestamp(0) without time zone,
    profileid integer,
    last_logon_date timestamp without time zone,
    status character varying(25) COLLATE pg_catalog."default",
    role character varying(15) COLLATE pg_catalog."default",
    componentid integer,
    CONSTRAINT bv_pk_peopleid PRIMARY KEY (peopleid)
        USING INDEX TABLESPACE pkihtts_data,
    CONSTRAINT tokenid_uniq UNIQUE (tokenid)
        INCLUDE(tokenid)
        USING INDEX TABLESPACE pkihtts_data,
    CONSTRAINT fk_tokenid FOREIGN KEY (tokenid)
        REFERENCES pkihtts.tb_token (tokenid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)
TABLESPACE pkihtts_data;

ALTER TABLE pkihtts.bv_tb_people
    OWNER to pkihtts;

bv_tb_people_his:

 CREATE TABLE pkihtts.bv_tb_people_his
(
    javaid integer NOT NULL DEFAULT nextval('pkihtts.bvsq_peoplehis_javaid'),
    peopleid integer NOT NULL,
    tokenid integer,
    auid character varying(25) COLLATE pg_catalog."default" NOT NULL,
    upn character varying(256) COLLATE pg_catalog."default" NOT NULL,
    firstname character varying(64) COLLATE pg_catalog."default",
    middlename character varying(64) COLLATE pg_catalog."default",
    lastname character varying(64) COLLATE pg_catalog."default" NOT NULL,
    genqual character varying(4) COLLATE pg_catalog."default",
    agency character varying(50) COLLATE pg_catalog."default",
    subagency character varying(64) COLLATE pg_catalog."default",
    affiliation character varying(10) COLLATE pg_catalog."default",
    subscribertype character varying(64) COLLATE pg_catalog."default",
    countryofcitizenship character varying(2) COLLATE pg_catalog."default",
    emailaddress character varying(256) COLLATE pg_catalog."default" NOT NULL,
    dateregistered timestamp(0) without time zone NOT NULL,
    comments character varying(255) COLLATE pg_catalog."default",
    isdeleted character varying(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'n'::character varying,
    modifiedby integer NOT NULL,
    modifieddate timestamp(0) without time zone,
    profileid integer,
    status character varying(25) COLLATE pg_catalog."default",
    component character varying(100) COLLATE pg_catalog."default",
    role character varying(15) COLLATE pg_catalog."default",
    componentid integer,
    CONSTRAINT bv_pk_peoplehis_javaid PRIMARY KEY (javaid)
        USING INDEX TABLESPACE pkihtts_data
)

TABLESPACE pkihtts_data;
ALTER TABLE pkihtts.tb_people_his
    OWNER to "pkihtts";

tb_token:

 CREATE TABLE pkihtts.bv_tb_token
(
    tokenid integer NOT NULL DEFAULT nextval('pkihtts.bvsq_tokenid'::regclass),
    internalshipmentid integer,
    tokenassignmentid integer,
    tokenserialno character varying(25) COLLATE pg_catalog."default" NOT NULL,
    alphakey character varying(10) COLLATE pg_catalog."default",
    statusdate timestamp without time zone NOT NULL,
    comments character varying(255) COLLATE pg_catalog."default",
    isdeleted character varying(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'n'::character varying,
    modifiedby integer NOT NULL,
    modifieddate timestamp without time zone,
    identity_verified character varying(1) COLLATE pg_catalog."default",
    new_token_required character varying(1) COLLATE pg_catalog."default",
    isreassigned smallint NOT NULL DEFAULT 0,
    identityverified smallint NOT NULL DEFAULT 0,
    newtokenrequired smallint NOT NULL DEFAULT 0,
    status character varying(25) COLLATE pg_catalog."default",
    prevstatus character varying(25) COLLATE pg_catalog."default",
    reason character varying(25) COLLATE pg_catalog."default",
    CONSTRAINT bv_pk_tokenid PRIMARY KEY (tokenid)
        USING INDEX TABLESPACE pkihtts_data
)
TABLESPACE pkihtts_data;

ALTER TABLE pkihtts.bv_tb_token
    OWNER to pkihtts;

sequences:

peopleid sequence:

 CREATE SEQUENCE pkihtts.bvsq_peopleid
    INCREMENT 1
    START 50
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE pkihtts.bvsq_peopleid
    OWNER TO "pkihtts";

javaid sequence:

 CREATE SEQUENCE pkihtts.bvsq_peoplehis_javaid
    INCREMENT 1
    START 50
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE pkihtts.bvsq_peoplehis_javaid
    OWNER TO "pkihtts";

here are my functions/triggers:

function tb_people_upd:

 CREATE FUNCTION pkihtts.bvtrigger_fct_tr_people_upd()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE

        v_ErrorCode     int;
        v_ErrorMsg      varchar(512);
        v_Module        varchar(31) = 'BVTR_PEOPLE_UPD';
        v_Os_User       varchar(30);
        v_Host          varchar(40);
BEGIN

---
-- Copy the record from tb_people_his to tb_peop;e
---

    INSERT INTO pkihtts.bv_tb_people_his (
        peopleid, 
        role, 
        status, 
        tokenid, 
        auid, 
        upn, 
        firstname, 
        middlename, 
        lastname, 
        genqual, 
        agency, 
        subagency, 
        affiliation, 
        subscribertype, 
        countryofcitizenship, 
        emailaddress, 
        dateregistered, 
        comments, 
        isdeleted, 
        modifiedby, 
        modifieddate,
        profileid, 
        componentid
        )
    VALUES (
        old.peopleid, 
        old.role, 
        old.status, 
        old.tokenid, 
        old.auid, 
        old.upn, 
        old.firstname, 
        old.middlename, 
        old.lastname, 
        old.genqual, 
        old.agency, 
        old.subagency, 
        old.affiliation, 
        old.subscribertype, 
        old.countryofcitizenship, 
        old.emailaddress, 
        old.dateregistered,
        old.comments, 
        old.isdeleted, 
        old.modifiedby,
        old.modifieddate.
        old.profileid, 
        old.componentid
        )
  ;

    RETURN NEW;

---
-- Exception error handler
---

exception
   when others then
        v_ErrorCode := SQLSTATE;
        v_ErrorMsg  := SQLERRM;
        v_Os_User   := CURRENT_USER;
        v_Host      := inet_server_addr();
                                   
    INSERT INTO  pkihtts.bv_tb_errorlog( tstamp, os_user, host, module, errorcode, errormsg )
    VALUES ( current_timestamp, v_Os_User, v_Host, v_Module, v_ErrorCode, v_ErrorMsg );
    RETURN NEW;
END;
$BODY$;
CREATE TRIGGER bvtr_people_upd
    BEFORE UPDATE 
    ON pkihtts.bv_tb_people
    FOR EACH ROW
    EXECUTE PROCEDURE pkihtts.bvtrigger_fct_tr_people_upd();

tb_people_ins:

 CREATE FUNCTION pkihtts.bvtrigger_fct_tr_people_ins()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE

        v_peopleid      int = 0;
        v_ErrorCode     int;
        v_ErrorMsg      varchar(512);
        v_Module        varchar(31) = 'TR_PEOPLE_INS';
        v_Os_User       varchar(30);
        v_Host          varchar(40);
BEGIN

    if TG_OP = 'INSERT' then
        IF new.peopleid IS null THEN
            SELECT nextval('pkihtts."bvsq_peopleid"') INTO v_peopleid;
            new.peopleid := v_peopleid;
        END IF;
    
        IF new.modifieddate is null then
            new.modifieddate := CURRENT_TIMESTAMP;
        END IF;
        
        RETURN NEW;
    END IF;
    
---
-- Exception error handler
---

exception
   when others then
        v_ErrorCode := SQLSTATE;
        v_ErrorMsg  := SQLERRM;
        v_Os_User   := CURRENT_USER;
        v_Host      := inet_server_addr();
                                   
    INSERT INTO  pkihtts.bv_tb_errorlog( tstamp, os_user, host, module, errorcode, errormsg )
    VALUES ( current_timestamp, v_Os_User, v_Host, v_Module, v_ErrorCode, v_ErrorMsg );
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER bvtr_people_ins
        BEFORE INSERT
        ON pkihtts.bv_tb_people
        FOR EACH ROW
        EXECUTE PROCEDURE pkihtts.bvtrigger_fct_tr_people_ins();

trigger tb_people_his_ins:

 CREATE FUNCTION pkihtts.bvtrigger_fct_tr_peoplehis_ins()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE

        v_javaid        int = 0;
        v_ErrorCode     int;
        v_ErrorMsg      varchar(512);
        v_Module        varchar(31) = 'BVTR_PEOPLEHIS_INS';
        v_Os_User       varchar(30);
        v_Host          varchar(40);
BEGIN

    if TG_OP = 'INSERT' then
        IF new.javaid IS null THEN
            SELECT nextval('pkihtts."bvsq_peoplehis_javaid"') INTO v_javaid;
            new.javaid := v_javaid;
        END IF;
        
        RETURN NEW;
    END IF;
    
---
-- Exception error handler
---

exception
   when others then
        v_ErrorCode := SQLSTAT;
        v_ErrorMsg  := SQLERRM;
        v_Os_User   := CURRENT_USER;
        v_Host      := inet_server_addr();
                                   
    INSERT INTO  pkihtts.bv_tb_errorlog( tstamp, os_user, host, module, errorcode, errormsg )
    VALUES ( current_timestamp, v_Os_User, v_Host, v_Module, v_ErrorCode, v_ErrorMsg );
    RETURN NEW;
END;
$BODY$;
CREATE TRIGGER bvtr_peoplehis_ins
    BEFORE INSERT
    ON pkihtts.bv_tb_people_his
    FOR EACH ROW
    EXECUTE PROCEDURE pkihtts.bvtrigger_fct_tr_peoplehis_ins();

максимальное значение для последовательностей javaid и peopleid равно 50, и я изменил текущее значение на 51.

 select max(peopleid) from pkihtts.bv_tb_people; 50
select max(javaid) from pkihtts.bv_tb_people_his; 50

когда я обновляю запись tb_people, я продолжаю получать ошибку 22P02 только в AWS

 UPDATE pkihtts.bv_tb_people
    SET lastname='TEST'
    WHERE tokenid='49';


ERROR:  invalid input syntax for integer: "0A000"
CONTEXT:  PL/pgSQL function bvtrigger_fct_tr_people_upd() line 75 at assignment
SQL state: 22P02

но он нормально обновился на промежуточном сервере REL и не вставлялся в таблицу истории (tb_people_his), когда я обновлял запись tb_people.

почему ошибка появляется в AWS, а не в Redhat? Как я могу решить эту проблему?

Ответ №1:

поскольку AWS содержит нечисловые коды ошибок, и вы пытаетесь сохранить нечисловое значение(«0A000») в переменную int v_ErrorCode

Комментарии:

1. спасибо, что дали мне знать, но это не имеет смысла, когда в прошлом году я создал пару триггеров функций с кодом v_error, так как int и скрипт работали нормально. Проблема в том, что эта ошибка выдает мне ошибку, та, что в Linux, не вставляет данные. В таблице tb_errorlog он регистрирует код ошибки 23502, но errormsg-это » нулевое значение в столбце «javaid» нарушает ограничение not null». Я изменил столбец «целое число javaid НЕ РАВНО НУЛЮ ПО УМОЛЧАНИЮ nextval(‘pkihtts.bvsq_peoplehis_javaid’)». и я больше не вижу журнала ошибок.

2. Я изменил столбец кода ошибки в tb_errorlog на varchar(20), и триггер был обновлен и вставлен нормально.

3. v_Errorcode по-прежнему объявляет int в триггере. Я также изменил значение последовательности, начиная с того места, где оно остановилось (текущее значение)

Метки: