1. Mixing it all together with debugging functions
Fantastic work, you've made it to the last lesson, now it's time to learn one more trick and put together much of what we've learned so far. We've been using unnamed or DO functions for all of our work so far; however, for this last part, we want to use named functions.
2. Named function overview
A named function looks a lot like a do function just with a different wrapper around the outside. First, we start by using the CREATE OR REPLACE FUNCTION statement followed by our function name and then any parameters in parenthesis, next we specific the return type with a RETURNS BOOLEAN AS then the body of the function we'll use $$ just like we did with the do statement, in fact after that point, it's exactly the same as do function.
3. A function for debugging
Now let's see this wrapper in a more concrete example for debugging a SQL statement. We're making a function called debug_statement that takes a SQL statement and returns True if it had to run a debugger on it; otherwise, it returns False. We begin by defining the function signature with the CREATE OR REPLACE FUNCTION statement and giving it the name debug_statement. We want our debug_statement function to take one argument that is a SQL statement and return a boolean that indicates if it had to debug that statement. Now we're ready to begin the function body, just like a do function, we start with variable declaration next. This time, we're going to store the SQLSTATE code in v_state, message text in v_msg, exception detail in v_detail, and the exception context in v_context. Now we can open the BEGIN block where our statements go. I prefer to use a wrapping block on all functions to make it easy to extend the functionality without needing to rework a significant amount of code later. So here we will begin a wrapper block, then begin our function block. Finally, we call EXECUTE, which will execute the statement stored in our sql_stmt variable.
4. The rest of a function for debugging
Now we're ready to declare our exception handler as we've done previously and record the details in the error table. Also, we want to return True to indicate that the function did require debugging within the exception handler. Then we close our inner block and return False, which means that the statement did not need debugging and ran cleanly. Finally, we wrap the whole function wrapper block and close the $$ string.
5. Using the function as a statement
We can use the function a few different ways; first, let's use it directly via a SELECT statement. We do this by specifying the debug_statement function just as we would any other SQL function with a select statement. Next, we pass in the SQL statement as a string here. Note that we have to escape any single quotes with two single quotes so that it will be a proper variable string. When we execute this statement, we get the output of t here, indicating that the statement was debugged.
6. Reviewing the functions recording of the exception
We can see that the supplied UPDATE statement causes a check constraint failure if we select from the errors table.
7. Using the function with in a function
Another way to use our debug_statement function is in the exception clause of another function! Here we have a DO function where we store the SQL statement as a string and call execute on it, if that causes an exception, we use the PERFORM keyword to call our debug_statement on the SQL that errored. Remember DO functions don't output anything.
8. Error recording from the DO function
A check of the error table shows the same error as before but with a proper context indicating how we got here. Now, this is a powerful tool! Consider the times we used a DO statement with nested blocks; we could use this as a centralized debugging function to get to exactly where things went wrong.
9. Let's practice!
Alright let's go put this to good use!