PostgreSQL Error Codes, set a custom error code

Question

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

Laurenz Albe · Accepted Answer · 2018-06-08 15:05:21Z

2

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.

answered Jun 8, 2018 at 15:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Error Codes, set a custom error code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related