Introduction
Side-effects in functions
Invoking functions
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
