How to change the text in the default error to readable in a stored procedure?

Question

PostgreSQL

Start over. Let's say I need to write a stored procedure (Postgresql, version 11) that would add data to a table and check if the user entered the correct data.

Those. something like this:
CREATE PROCEDURE add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
AS $$
BEGIN
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
END;
$$LANGUAGE 'plpgsql';

Then this procedure is called, for example like this:
add_product_4('2', '2019.11.12 12:23:24', 'April', 'Citramon', '18', '105');

So far so good. BUT, if a string value is inserted into the last variable,
"ERROR: ERROR: invalid value for integer: "df"
LINE 2: ...', '2019.11.12 12:23:24', 'April', 'Citramon', '18', 'df');
^
SQL state: 22P02
Symbol: 78"

My task is to make this error readable.

I tried it like this:
CREATE PROCEDURE add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
AS $$
BEGIN
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
EXCEPTION
WHEN invalid_text_representation THEN
RAISE NOTICE 'Error: invalid variable type';
END;
$$LANGUAGE '

Those. knowing the error code (22P02), I went to the site: https://www.postgresql.org/docs/11/errcodes-append ...
and found out what this error is called, after which I inserted this name after the EXCEPTION WHEN construct. And I applied the RAISE NOTICE construct for that. so that the client now displays the text I specified.

But in the end, after the call is made, nothing changes, the client still displays the default answer (see picture).


I watched a video tutorial where a person does the same thing and he succeeds. I can't figure out what am I doing wrong?
The task itself was received from the employer, i.е. this is a test task, so it is important to do exactly as asked.

Answer 1

exception at you it is anchored to insert, and the error arises at a procedure call. That is, you call the procedure
add_product_4 (INT, TIMESTAMP, VARCHAR, VARCHAR, INT, VARCHAR)
and you only have the procedure:
add_product_4 (INT, TIMESTAMP, VARCHAR, VARCHAR, INT, INT)
, i.e. does not even reach execution of INSERT.
Type matching must be taken into account. You can, of course, create a procedure with the required type by overloading (it seems that there is an overload in PostgreSQL), and you can just throw a notation there, but this is a perversion. If the input variable of the procedure has an incorrect type, this is not an exception, this is an error in the code, reconsider your approach.

Answer 2

You don't have the add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f text) function, but since the procedure is called as 'df') - let's try the existing one, and for this you need to cast 6 argument to number. Which is impossible.
Bottom line: no matter what you write in the procedure, it is not called at all.
Add a variant of the procedure that accepts text and check in the procedure whether there is a number in this text.

Answer 3

The user himself does not insert values ​​into the database functions. Do validation outside of the database.