Exception handling (BEGIN-EXCEPTION-END) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this exception block?

Consider the following PL/pgSQL block. What will be the output after execution?

PostgreSQL

DO $$
DECLARE
  result TEXT := '';
BEGIN
  BEGIN
    RAISE EXCEPTION 'Error occurred';
  EXCEPTION
    WHEN OTHERS THEN
      result := 'Exception caught';
  END;
  RAISE NOTICE '%', result;
END $$;

A Exception caught

B Error occurred

C No output

D Syntax error

💡 Hint

Look at how the inner BEGIN-EXCEPTION-END block handles the error.

The inner block raises an exception, which is caught by the EXCEPTION clause, setting result to 'Exception caught'. The outer block then raises a notice with that value.

📝 Syntax

intermediate

Identify the syntax error in this exception block

Which option correctly identifies the syntax error in this PL/pgSQL code snippet?

PostgreSQL

DO $$
BEGIN
  BEGIN
    RAISE EXCEPTION 'Oops';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Caught';
  END;
END $$;

A Missing THEN keyword after WHEN OTHERS

B Missing END for inner BEGIN block

C RAISE NOTICE cannot be used inside EXCEPTION

D Missing semicolon after RAISE EXCEPTION

💡 Hint

Check the syntax of the EXCEPTION WHEN clause.

In PL/pgSQL, the WHEN clause must be followed by THEN before the handler code.

❓ optimization

advanced

Optimize exception handling to avoid unnecessary overhead

Which option shows the best practice to avoid performance overhead when no exception is expected?

PostgreSQL

DO $$
BEGIN
  -- Some code that rarely raises exceptions
  PERFORM some_function();
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Exception caught';
END $$;

A Use nested BEGIN blocks with EXCEPTION inside the main block

B Remove the EXCEPTION block entirely if exceptions are rare and can be handled outside

C Replace WHEN OTHERS with WHEN SQLSTATE '23505' THEN

D Keep the EXCEPTION block as is to always catch errors

💡 Hint

Exception handling adds overhead even if no error occurs.

If exceptions are rare and can be handled at a higher level, removing the EXCEPTION block improves performance.

🔧 Debug

advanced

Does this exception block catch the error?

Given this PL/pgSQL block, which statement about the exception handling is correct?

PostgreSQL

DO $$
BEGIN
  PERFORM 1/0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero caught';
END $$;

A The exception name 'division_by_zero' is incorrect; it should be 'division_by_zero_error'

B The exception is not caught because the error is raised outside a BEGIN block

C The exception name should be SQLSTATE code '22012' instead of the name

D The exception name is correct and will catch the error

💡 Hint

Check the exact exception name or SQLSTATE code for division by zero in PostgreSQL.

The exception name 'division_by_zero' is a predefined condition name corresponding to SQLSTATE '22012' and will correctly catch the division by zero error.

🧠 Conceptual

expert

What happens when an exception is re-raised inside an EXCEPTION block?

Consider this PL/pgSQL block. What will be the final output?

PostgreSQL

DO $$
BEGIN
  BEGIN
    RAISE EXCEPTION 'Initial error';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Caught error';
      RAISE;
  END;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Outer handler caught error';
END $$;

A Only 'Outer handler caught error' notice will be shown

B Only 'Caught error' notice will be shown

C The output will be two notices: 'Caught error' and 'Outer handler caught error'

D No notices will be shown because the exception is unhandled

💡 Hint

Think about how re-raising an exception propagates it to outer blocks.

The inner block catches and raises a notice, then re-raises the exception. The outer block catches it and raises another notice.