Creating named functions and declaring variables
Now that you've seen a powerful debugging function in action, let's build one of your own. First, start by using defining the function signature which supplied the function name, any parameters, and a return type. After that point, it's the same as a DO
function.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Define a function named
debug_statement
that takes a SQL statement assql_stmt
. - The return type of the function should be a
BOOLEAN
. - The function should execute the supplied SQL statement and catch any exception.
- The function should return
True
if it triggers debugging andFalse
if it does not.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Define our function signature
___ ___ ___ ___ debug_statement(
sql_stmt TEXT
)
-- Declare our return type
___ ___ AS $$
DECLARE
exc_state TEXT;
exc_msg TEXT;
exc_detail TEXT;
exc_context TEXT;
BEGIN
BEGIN
-- Execute the statement passed in
___ sql_stmt;
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS
exc_state = RETURNED_SQLSTATE,
exc_msg = MESSAGE_TEXT,
exc_detail = PG_EXCEPTION_DETAIL,
exc_context = PG_EXCEPTION_CONTEXT;
INSERT into errors (msg, state, detail, context) values (exc_msg, exc_state, exc_detail, exc_context);
-- Return True to indicate the statement was debugged
___ ___;
END;
-- Return False to indicate the statement was not debugged
RETURN ___;
END;
$$ LANGUAGE plpgsql;
SELECT debug_statement('INSERT INTO patients (a1c, glucose, fasting) values (20, 89, TRUE);')