How to implement custom exception handling of PostgreSQL function
This article mainly shows you "how to achieve PostgreSQL function custom exception handling", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "how to achieve PostgreSQL function custom exception handling" this article.
Code handling also requires imagination to make the impossible possible. Here is an example.
1. Someone asked PostgreSQL if there were any custom exceptions, and Oracle did:
-- define myex Exception;-- to throw RAISE myex;-- capture WHEN myex THEN
Simple and easy to use
2. Let's take a look at PostgreSQL's PL/pgSQL
RAISE [level] condition_name [USING option = expression [,...]]; RAISE [level] SQLSTATE 'sqlstate' [USING option = expression [,...]]
These two grammars seem to have some flexibility, but in fact they can only use predefined exceptions, which are stated in the documentation and will report an error if they are not recognized.
ERROR: unrecognized exception condition "xxxxxxx" CONTEXT: compilation of PL/pgSQL function "func_a" near line 3ERROR: invalid SQLSTATE code at or near "'12345'" LINE 5: RAISE SQLSTATE' 12345' USING MESSAGE = 'zzz'
3. Code implementation
This paragraph is a forced addition to the play to prevent the length of the exception from being too small, which can be skipped without any impact.
For (I = 0; exception_label_ map [I] .label! = NULL; imap +) {if (strcmp (condname, exception_label_ map [I] .label) = = 0) return exception_label_ map [I] .sqlerrstate;}
As you can see here, the names or codes that can be used are determined at compile time and cannot be defined by yourself.
However, there are still ways to do it
Try to choose a context-free error, that is, an exception that this code is unlikely to throw, to avoid program errors being intercepted by errors.
For example: 0A000 feature_not_supported
We can throw an exception and mark its particularity with an error message:
RAISE SQLSTATE '0A000' USING MESSAGE =' flying0001:failed to...'
It can be handled conditionally when it is captured:
WHEN SQLSTATE '0A000' THEN DECLARE m text; BEGIN GET STACKED DIAGNOSTICS m = MESSAGE_TEXT; IF (strpos (m,' flying0001:') = 1) THEN RAISE WARNING 'got flying0001'; ELSIF. END IF; END
Although verbose, but it does achieve the same function.
5. Complete demonstration
It is also to add to the play and put together the space.
CREATE OR REPLACE FUNCTION func_a () RETURNS void AS$$BEGINRAISE SQLSTATE '0A000' USING MESSAGE =' flying0001:Do one thing at a time, and do well.';EXCEPTIONWHEN feature_not_supported THEN DECLARE m text; BEGIN GET STACKED DIAGNOSTICS m = MESSAGE_TEXT; IF (strpos (m, 'flying0001:') = 1) THEN RAISE WARNING' got flying0001'; END IF; END;END;$$LANGUAGE plpgsql These are all the contents of the article "how to implement custom exception handling for PostgreSQL functions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
