PostgreSQL How to use standard ERROR CODE and ERROR MESSAGE

Question

I would like to use standard ERROR message and code of PostgreSQL.
Right now, I can achieve my goal, but I would like to know the best way I can use.
Here is my current function in database.

CREATE OR REPLACE FUNCTION Dugong.Forget_Passwd_Add(
       in_Username varchar(255),
       in_randomURLs text
)
RETURNS void AS $$
BEGIN
        IF (SELECT Dugong.Users_isExist(in_Username)) THEN
        INSERT INTO dugong.Forget_Passwd (Username, randomURLs, used)
        VALUES (in_Username, in_randomURLs, FALSE);
        ELSE
                RAISE EXCEPTION USING ERRCODE = '20000', MESSAGE = 'Username not found';
        END IF;
END;
$$ LANGUAGE PLPGSQL;

My Node Express with pg module will use client.query().
client.query() has callback err and result.
With my example in here. I can use err.message and err.code to get error message and error code respectively.
Question :
I want my code be able to RAISE any error from database side not restrict only my specific error code.
How can I do that?

Same question: stackoverflow.com/questions/2699958/…

vitaly-t
– vitaly-t

2016-03-19 09:45:21 +00:00
Commented Mar 19, 2016 at 9:45 — vitaly-t
– vitaly-t, Commented Mar 19, 2016 at 9:45

Egor Rogov · Accepted Answer · 2016-03-19 10:44:44Z

1

You can use raise to throw both custom and standard exceptions.

Reference standard exceptions either by it's code:

raise sqlstate '22012';

or by name:

raise division_by_zero;

Here is the list of defined error codes and names; more information about raise: "Errors and messages".

answered Mar 19, 2016 at 10:44

Egor Rogov

5,48828 silver badges40 bronze badges

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

3 Comments

joe Over a year ago

Suppose. I want my code be able to raise any error beyond username not found. For example disk is full error and any error I can not imagine right now. Do I need to hardcode for case by case?

Egor Rogov Over a year ago

Either hardcode, or maintain your own mapping for error codes and messages (just like stackoverflow.com/questions/2699958/… suggests).

joe Over a year ago

Thank you. Although I am not fully understand your answer, but I will back with my study again later thank you.

Collectives™ on Stack Overflow

PostgreSQL How to use standard ERROR CODE and ERROR MESSAGE

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related