It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Side-effects in functions
8. Throwing exceptions

Instruction

Of course, we can raise exceptions if they are needed. Consider the case where a function receives a nonexistent product ID as an input parameter. In this situation, we should raise/throw an exception, as shown in the example below:

CREATE FUNCTION remove_product(prod_id smallint)
RETURNS void AS $$
BEGIN
  IF NOT EXISTS(SELECT (1) FROM product WHERE product_id = prod_id) THEN
    RAISE EXCEPTION 'Nonexistent product ID --> %', prod_id
    USING HINT = 'Please check the product ID';
  ELSE
    UPDATE product
    SET
      is_available = 0,
      is_active = 0
    WHERE product_id = prod_id;

    DELETE FROM prod_inventory
    WHERE product_id = prod_id;
  END IF;
END;
$$ LANGUAGE plpgsql;

Exercise

Amend the remove_user_account() function we created in the previous exercise so it raises an exception when a record with a given ID doesn't exist in the user_account table:

Nonexistent user account ID --> %

with a hint:

Please check the user account ID