CODEWITHSUNDEEP
postgresqlerror-handlingtriggers
Naty Bizz
Naty Bizz

Reputation: 2342

PostgreSQL Error Codes, set a custom error code

I'm translating a Oracle trigger to Postgres; I have this so far translated in postgres

DROP TRIGGER IF EXISTS trg_test_biud ON mytable CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_trg_test_biud() RETURNS trigger AS $BODY$
DECLARE
    id_ double precision := NULL;
    hour_ varchar(10) := NULL;
BEGIN

    /* INSERT */
    IF TG_OP = 'INSERT' THEN
        BEGIN
            select nextval('myschema.id_audit_mytable_seq') into id_;
            SELECT  TO_CHAR(current_timestamp, 'HH24:MI:SS') INTO hour_;
            INSERT INTO myschema.audit_mytable(id, id_mytable, user_name, event, myhour, hour, geometry)
                VALUES (id_, NEW.code, NEW.user_name, 'INSERT', LOCALTIMESTAMP, hour_, NEW.GEOMETRY);
            RETURN NEW;
            EXCEPTION
            WHEN OTHERS THEN
                RAISE EXCEPTION '%', 'Error when insert into audit_mytable: ' || sqlerrm USING ERRCODE = '-20000';

        END;

    END IF;

END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER trg_test_biud
    BEFORE INSERT OR UPDATE OR DELETE ON myschema.mytable FOR EACH ROW
    EXECUTE PROCEDURE trigger_fct_trg_test_biud();

When the exception is raised, I get this error:

ERROR: unrecognized exception condition «-20000»
SQL state: 42704

Does this has to do with the fact that in Oracle the 'custom error code' is a negative number? postgres does not recognize this? I checked this page, but it says nothing about negative numbers: https://www.postgresql.org/docs/current/static/errcodes-appendix.html

Upvotes: 2

Views: 1247

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246718

The Oracle number -20000 is not an SQLSTATE, but a proprietary error code.

You have to specify one of the 5-character SQLSTATEs defined in appendix A of the documentation.

Upvotes: 2

Related Questions