{"id":1639,"date":"2025-01-12T12:42:45","date_gmt":"2025-01-12T05:42:45","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1639"},"modified":"2025-01-12T12:52:51","modified_gmt":"2025-01-12T05:52:51","slug":"plpgsql-exception","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/","title":{"rendered":"PL\/pgSQL Exception"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn how to handle exceptions using the PL\/pgSQL <code>EXCEPTION<\/code> block.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='introduction-to-the-plpgsql-exception-block'>Introduction to the PL\/pgSQL EXCEPTION Block <a href=\"#introduction-to-the-plpgsql-exception-block\" class=\"anchor\" id=\"introduction-to-the-plpgsql-exception-block\" title=\"Anchor for Introduction to the PL\/pgSQL EXCEPTION Block\">#<\/a><\/h2>\n\n\n\n<p>In PL\/pgSQL, an exception is an unexpected condition or error during execution. Exceptions may result from issues like constraint violation, division by zero, or data not found.<\/p>\n\n\n\n<p>To handle exceptions, you use the <code>EXCEPTION<\/code> block within the <code>BEGIN<\/code> and <code>END<\/code> keywords of a block:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">DO<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">DECLARE<\/span>\n   <span class=\"hljs-comment\">-- declaration<\/span>\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-comment\">-- code that may cause exceptions<\/span>\n<span class=\"hljs-keyword\">EXCEPTION<\/span>\n    <span class=\"hljs-keyword\">WHEN<\/span> exception_name <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-comment\">-- Handle the specific exception<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'An error occurred: %'<\/span>, <span class=\"hljs-built_in\">SQLERRM<\/span>;\n    <span class=\"hljs-keyword\">WHEN<\/span> OTHERS <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-comment\">-- Handle all other exceptions<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'An unknown error occurred: %'<\/span>, <span class=\"hljs-built_in\">SQLERRM<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>WHEN<\/code> clause catches specific exceptions such as division by zero or unique constraint violation.<\/li>\n\n\n\n<li>The <code>WHEN OTHERS<\/code> clause catches all exceptions you have not explicitly handled.<\/li>\n<\/ul>\n\n\n\n<p>The <code>SQLERRM<\/code> and <code>SQLSTATE<\/code> are error message variables:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <code>SQLERRM<\/code> variable contains the exception&#8217;s message.<\/li>\n\n\n\n<li>The <code>SQLSTATE<\/code> variable holds the error code.<\/li>\n<\/ul>\n\n\n\n<p>For a comprehensive list of exception names and SQL error codes, check out this <a href=\"https:\/\/www.postgresql.org\/docs\/current\/errcodes-appendix.html\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">SQL Error Codes page<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='handling-a-division-by-zero-exception-example'>Handling a Division by Zero Exception Example <a href=\"#handling-a-division-by-zero-exception-example\" class=\"anchor\" id=\"handling-a-division-by-zero-exception-example\" title=\"Anchor for Handling a Division by Zero Exception Example\">#<\/a><\/h2>\n\n\n\n<p>The following statement <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\">create a function<\/a> called <code>try_divide<\/code> that returns the division of two operands:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> try_divide(x <span class=\"hljs-type\">NUMERIC<\/span>, y <span class=\"hljs-type\">NUMERIC<\/span>) \n<span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-type\">NUMERIC<\/span> <span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">RETURN<\/span> x \/ y;\n<span class=\"hljs-keyword\">EXCEPTION<\/span>\n    <span class=\"hljs-keyword\">WHEN<\/span> <span class=\"hljs-built_in\">division_by_zero<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'Cannot divide by zero, returning NULL!'<\/span>;\n        <span class=\"hljs-keyword\">RETURN<\/span> <span class=\"hljs-keyword\">NULL<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIEZVTkNUSU9OIHRyeV9kaXZpZGUoeCBOVU1FUklDLCB5IE5VTUVSSUMpIApSRVRVUk5TIE5VTUVSSUMgQVMgCiQkCkJFR0lOCiAgICBSRVRVUk4geCAvIHk7CkVYQ0VQVElPTgogICAgV0hFTiBkaXZpc2lvbl9ieV96ZXJvIFRIRU4KICAgICAgICBSQUlTRSBOT1RJQ0UgJ0Nhbm5vdCBkaXZpZGUgYnkgemVybywgcmV0dXJuaW5nIE5VTEwhJzsKICAgICAgICBSRVRVUk4gTlVMTDsKRU5EOwokJApMQU5HVUFHRSBwbHBnc3FsOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>How the function works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Divide <code>x<\/code> by <code>y<\/code> and return the result.<\/li>\n\n\n\n<li>If <code>y<\/code> is zero, a <code>division_by_zero<\/code> exception will occur. The <code>EXCEPTION<\/code> block handles it by raising a notice and returning <code>NULL<\/code>.<\/li>\n<\/ul>\n\n\n\n<p>For example, the following statement calls the <code>try_divide<\/code> to divide 10 by 2:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> try_divide(<span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">2<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIHRyeV9kaXZpZGUoMTAsIDIpOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">     try_divide\n<span class=\"hljs-comment\">--------------------<\/span>\n <span class=\"hljs-number\">5.0000000000000000<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>However, when you divide 10 by zero, it will return <code>NULL<\/code> and issue a notice:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> try_divide(<span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">0<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIHRyeV9kaXZpZGUoMTAsIDApOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">NOTICE<\/span>: Cannot divide <span class=\"hljs-keyword\">by<\/span> zero, <span class=\"hljs-keyword\">returning<\/span> <span class=\"hljs-keyword\">NULL<\/span>!<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='handling-a-no_data_found-exception-example'>Handling a NO_DATA_FOUND Exception Example <a href=\"#handling-a-no_data_found-exception-example\" class=\"anchor\" id=\"handling-a-no_data_found-exception-example\" title=\"Anchor for Handling a NO_DATA_FOUND Exception Example\">#<\/a><\/h2>\n\n\n\n<p>The <code>NO_DATA_FOUND<\/code> exception occurs when a <code><a href=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-select-into\/\">SELECT INTO<\/a><\/code> statement does not retrieve any row.<\/p>\n\n\n\n<p>The following example <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-function\/\">creates a function<\/a> called <code>get_inventory_quantity<\/code> that get an inventory quantity of a product specified by a product id:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> get_inventory_quantity (id <span class=\"hljs-type\">INT<\/span>) \n<span class=\"hljs-keyword\">RETURNS<\/span> <span class=\"hljs-type\">INT<\/span> <span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">DECLARE<\/span> \n     v_qty <span class=\"hljs-type\">INT<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span> quantity <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">STRICT<\/span> v_qty \n    <span class=\"hljs-keyword\">FROM<\/span> inventories\n    <span class=\"hljs-keyword\">WHERE<\/span> product_id = id;\n\n    <span class=\"hljs-keyword\">RETURN<\/span> v_qty;\n\n<span class=\"hljs-keyword\">EXCEPTION<\/span>\n    <span class=\"hljs-keyword\">WHEN<\/span> <span class=\"hljs-built_in\">NO_DATA_FOUND<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'No product found with the id %'<\/span>, id;\n        <span class=\"hljs-keyword\">RETURN<\/span> <span class=\"hljs-keyword\">NULL<\/span>;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIEZVTkNUSU9OIGdldF9pbnZlbnRvcnlfcXVhbnRpdHkgKGlkIElOVCkgClJFVFVSTlMgSU5UIEFTIAokJApERUNMQVJFIAogICAgIHZfcXR5IElOVDsKQkVHSU4KICAgIFNFTEVDVCBxdWFudGl0eSBJTlRPIFNUUklDVCB2X3F0eSAKICAgIEZST00gaW52ZW50b3JpZXMKICAgIFdIRVJFIHByb2R1Y3RfaWQgPSBpZDsKCiAgICBSRVRVUk4gdl9xdHk7CgpFWENFUFRJT04KICAgIFdIRU4gTk9fREFUQV9GT1VORCBUSEVOCiAgICAgICAgUkFJU0UgTk9USUNFICdObyBwcm9kdWN0IGZvdW5kIHdpdGggdGhlIGlkICUnLCBpZDsKICAgICAgICBSRVRVUk4gTlVMTDsKRU5EOwokJApMQU5HVUFHRSBwbHBnc3FsOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>How the function works:<\/p>\n\n\n\n<p>First, retrieve the quantity of the product specified by <code>id<\/code> and assign it to the <code>v_qty<\/code> variable using the <code>SELECT INTO<\/code> statement with the <code>STRICT<\/code> option:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT quantity \nINTO STRICT v_qty\nFROM inventories\nWHERE product_id = id;<\/code><\/span><\/pre>\n\n\n<p>Second, return the quantity:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">RETURN<\/span> v_qty;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, if the product with the <code>id<\/code> does not exist, the <code>NO_DATA_FOUND<\/code> exception occurs. In this case, we raise a notice and return <code>NULL<\/code> in the <code>EXCEPTION<\/code> block:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">EXCEPTION<\/span>\n    WHEN NO_DATA_FOUND THEN\n        RAISE NOTICE <span class=\"hljs-string\">'No product found with the id %'<\/span>, id;\n        <span class=\"hljs-keyword\">RETURN<\/span> <span class=\"hljs-keyword\">NULL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following statement calls the <code>get_inventory_quantity<\/code> function to get the inventory quantity of the product with id <code>9999<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span> get_inventory_quantity (<span class=\"hljs-number\">9999<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">NOTICE<\/span>:   <span class=\"hljs-keyword\">No<\/span> product <span class=\"hljs-built_in\">found<\/span> <span class=\"hljs-keyword\">with<\/span> the id <span class=\"hljs-number\">9999<\/span>\n get_inventory_quantity\n<span class=\"hljs-comment\">------------------------<\/span>\n                   <span class=\"hljs-keyword\">NULL<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='handling-a-too_many_rows-exception'>Handling a TOO_MANY_ROWS Exception <a href=\"#handling-a-too_many_rows-exception\" class=\"anchor\" id=\"handling-a-too_many_rows-exception\" title=\"Anchor for Handling a TOO_MANY_ROWS Exception\">#<\/a><\/h2>\n\n\n\n<p>The following creates a function that finds a product by safety stock:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">FUNCTION<\/span> find_product_by_safety_stock (qty <span class=\"hljs-type\">INT<\/span>) \n<span class=\"hljs-keyword\">RETURNS<\/span> products \n<span class=\"hljs-keyword\">AS<\/span> \n$$<span class=\"pgsql\">\n<span class=\"hljs-keyword\">DECLARE<\/span> \n     v_product products<span class=\"hljs-meta\">%ROWTYPE<\/span>;\n<span class=\"hljs-keyword\">BEGIN<\/span>\n    <span class=\"hljs-keyword\">SELECT<\/span> *  \n    <span class=\"hljs-keyword\">FROM<\/span> products\n    <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">STRICT<\/span> v_product \n    <span class=\"hljs-keyword\">WHERE<\/span> safety_stock = qty;\n\n    <span class=\"hljs-keyword\">RETURN<\/span> v_product;\n\n<span class=\"hljs-keyword\">EXCEPTION<\/span>\n    <span class=\"hljs-keyword\">WHEN<\/span> <span class=\"hljs-built_in\">NO_DATA_FOUND<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'No product found with the safety stock qty %'<\/span>, qty;\n        <span class=\"hljs-keyword\">RETURN<\/span> <span class=\"hljs-keyword\">NULL<\/span>;\n    <span class=\"hljs-keyword\">WHEN<\/span> <span class=\"hljs-built_in\">TOO_MANY_ROWS<\/span> <span class=\"hljs-keyword\">THEN<\/span>\n        <span class=\"hljs-keyword\">RAISE<\/span> <span class=\"hljs-keyword\">NOTICE<\/span> <span class=\"hljs-string\">'More than one product found with the safety stock qty %. Getting the one with the highest price'<\/span>, qty;\n\n        <span class=\"hljs-keyword\">SELECT<\/span> *  \n        <span class=\"hljs-keyword\">FROM<\/span> products\n        <span class=\"hljs-keyword\">INTO<\/span> <span class=\"hljs-keyword\">STRICT<\/span> v_product \n        <span class=\"hljs-keyword\">WHERE<\/span> safety_stock = qty  \n        <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> price <span class=\"hljs-keyword\">DESC<\/span>\n        <span class=\"hljs-keyword\">LIMIT<\/span> <span class=\"hljs-number\">1<\/span>;\n\n        <span class=\"hljs-keyword\">RETURN<\/span> v_product;\n<span class=\"hljs-keyword\">END<\/span>;\n$$<\/span>\n<span class=\"hljs-keyword\">LANGUAGE<\/span> plpgsql;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIEZVTkNUSU9OIGZpbmRfcHJvZHVjdF9ieV9zYWZldHlfc3RvY2sgKHF0eSBJTlQpIApSRVRVUk5TIHByb2R1Y3RzIApBUyAKJCQKREVDTEFSRSAKICAgICB2X3Byb2R1Y3QgcHJvZHVjdHMlUk9XVFlQRTsKQkVHSU4KICAgIFNFTEVDVCAqICAKICAgIEZST00gcHJvZHVjdHMKICAgIElOVE8gU1RSSUNUIHZfcHJvZHVjdCAKICAgIFdIRVJFIHNhZmV0eV9zdG9jayA9IHF0eTsKCiAgICBSRVRVUk4gdl9wcm9kdWN0OwoKRVhDRVBUSU9OCiAgICBXSEVOIE5PX0RBVEFfRk9VTkQgVEhFTgogICAgICAgIFJBSVNFIE5PVElDRSAnTm8gcHJvZHVjdCBmb3VuZCB3aXRoIHRoZSBzYWZldHkgc3RvY2sgcXR5ICUnLCBxdHk7CiAgICAgICAgUkVUVVJOIE5VTEw7CiAgICBXSEVOIFRPT19NQU5ZX1JPV1MgVEhFTgogICAgICAgIFJBSVNFIE5PVElDRSAnTW9yZSB0aGFuIG9uZSBwcm9kdWN0IGZvdW5kIHdpdGggdGhlIHNhZmV0eSBzdG9jayBxdHkgJS4gR2V0dGluZyB0aGUgb25lIHdpdGggdGhlIGhpZ2hlc3QgcHJpY2UnLCBxdHk7CgogICAgICAgIFNFTEVDVCAqICAKICAgICAgICBGUk9NIHByb2R1Y3RzCiAgICAgICAgSU5UTyBTVFJJQ1Qgdl9wcm9kdWN0IAogICAgICAgIFdIRVJFIHNhZmV0eV9zdG9jayA9IHF0eSAgCiAgICAgICAgT1JERVIgQlkgcHJpY2UgREVTQwogICAgICAgIExJTUlUIDE7CgogICAgICAgIFJFVFVSTiB2X3Byb2R1Y3Q7CkVORDsKJCQKTEFOR1VBR0UgcGxwZ3NxbDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>How the function works:<\/p>\n\n\n\n<p>The function finds the product with the specific safety stock from the <code>products<\/code> table. If there is more than one product, the <code>EXCEPTION<\/code> block raises a notice and returns the product with the highest price.<\/p>\n\n\n\n<p>The following statement calls the <code>find_product_by_safety_stock<\/code> function to find the product with a safety stock of 10:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  product_name,\n  safety_stock,\n  price\n<span class=\"hljs-keyword\">FROM<\/span>\n  find_product_by_safety_stock (<span class=\"hljs-number\">10<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=U0VMRUNUIHByb2R1Y3RfbmFtZSwgc2FmZXR5X3N0b2NrLCBwcmljZSBGUk9NIGZpbmRfcHJvZHVjdF9ieV9zYWZldHlfc3RvY2sgKDEwKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">NOTICE<\/span>:   More than a product <span class=\"hljs-built_in\">found<\/span> <span class=\"hljs-keyword\">with<\/span> the safety stock qty <span class=\"hljs-number\">10<\/span>\n   product_name   | safety_stock | price\n<span class=\"hljs-comment\">------------------+--------------+--------<\/span>\n Sony Xperia <span class=\"hljs-number\">1<\/span> VI |           <span class=\"hljs-number\">10<\/span> | <span class=\"hljs-number\">949.99<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the <code>EXCEPTION<\/code> block to handle exceptions that occur during the execution of your PL\/pgSQL code.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=plpgsql-exception\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1639\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/\"\n\t\t\t\tdata-post-title=\"PL\/pgSQL Exception\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1639\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/\"\n\t\t\t\tdata-post-title=\"PL\/pgSQL Exception\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you&#8217;ll learn how to handle exceptions using the PL\/pgSQL EXCEPTION block. Introduction to the PL\/pgSQL EXCEPTION Block # In PL\/pgSQL, an exception is an unexpected condition or error during execution. Exceptions may result from issues like constraint violation, division by zero, or data not found. To handle exceptions, you use the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1420,"menu_order":14,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1639","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PL\/pgSQL Exception<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to handle exceptions, including division by zero and no data found, using the PL\/pgSQL EXCEPTION block.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PL\/pgSQL Exception\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to handle exceptions, including division by zero and no data found, using the PL\/pgSQL EXCEPTION block.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-12T05:52:51+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/plpgsql-exception\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/plpgsql-exception\\\/\",\"name\":\"PL\\\/pgSQL Exception\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"datePublished\":\"2025-01-12T05:42:45+00:00\",\"dateModified\":\"2025-01-12T05:52:51+00:00\",\"description\":\"In this tutorial, you'll learn how to handle exceptions, including division by zero and no data found, using the PL\\\/pgSQL EXCEPTION block.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/plpgsql-exception\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/plpgsql-exception\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/plpgsql-exception\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PL\\\/pgSQL\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/plpgsql\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PL\\\/pgSQL Exception\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PL\/pgSQL Exception","description":"In this tutorial, you'll learn how to handle exceptions, including division by zero and no data found, using the PL\/pgSQL EXCEPTION block.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/","og_locale":"en_US","og_type":"article","og_title":"PL\/pgSQL Exception","og_description":"In this tutorial, you'll learn how to handle exceptions, including division by zero and no data found, using the PL\/pgSQL EXCEPTION block.","og_url":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-01-12T05:52:51+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/","url":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/","name":"PL\/pgSQL Exception","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"datePublished":"2025-01-12T05:42:45+00:00","dateModified":"2025-01-12T05:52:51+00:00","description":"In this tutorial, you'll learn how to handle exceptions, including division by zero and no data found, using the PL\/pgSQL EXCEPTION block.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/plpgsql\/plpgsql-exception\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PL\/pgSQL","item":"https:\/\/www.pgtutorial.com\/plpgsql\/"},{"@type":"ListItem","position":3,"name":"PL\/pgSQL Exception"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1639","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=1639"}],"version-history":[{"count":10,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1639\/revisions"}],"predecessor-version":[{"id":1650,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1639\/revisions\/1650"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/1420"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=1639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}