PostgreSQL

In PostgreSQL, a stored procedure is a set of SQL commands that you can save and reuse. Stored procedures help to encapsulate complex operations and business logic within the database, improving performance and maintainability. They can be executed with a single call and can accept input parameters, return results, and handle transactions. This allows for more efficient and organized database operations, especially in applications that require repeated and complex operations.

Steps to Create and Use Stored Procedures

1. Creating a Stored Procedure

   To create a stored procedure, use the CREATE PROCEDURE statement. Below is an example of how to create a procedure that processes a bank transaction:

   CREATE OR REPLACE PROCEDURE process_transaction(
       IN account_id INT,
       IN transaction_amount NUMERIC
   )
   LANGUAGE plpgsql
   AS $$
   BEGIN
       -- Deduct amount from account balance
       UPDATE accounts
       SET balance = balance - transaction_amount
       WHERE id = account_id;
   
       -- Record the transaction
       INSERT INTO transactions (account_id, amount, transaction_date)
       VALUES (account_id, transaction_amount, NOW());
   END;
   $$;
   

2. Calling a Stored Procedure

   To call a stored procedure, use the CALL statement. Here’s how to call the process_transaction procedure:

   CALL process_transaction(12345, 100.00);
   

3. Handling Errors in Stored Procedures

   Error handling is crucial for maintaining the integrity of your transactions. Use the EXCEPTION block to handle errors:

   CREATE OR REPLACE PROCEDURE safe_process_transaction(
       IN account_id INT,
       IN transaction_amount NUMERIC
   )
   LANGUAGE plpgsql
   AS $$
   BEGIN
       BEGIN
           -- Attempt to deduct amount from account balance
           UPDATE accounts
           SET balance = balance - transaction_amount
           WHERE id = account_id;
   
           -- Record the transaction
           INSERT INTO transactions (account_id, amount, transaction_date)
           VALUES (account_id, transaction_amount, NOW());
       EXCEPTION
           WHEN others THEN
               RAISE NOTICE 'Transaction failed: %', SQLERRM;
       END;
   END;
   $$;
   

4. Listing Stored Procedures

   To list all stored procedures in your database, you can query the pg_proc catalog:

   SELECT proname FROM pg_proc;
   

5. Dropping a Stored Procedure

   If you need to remove a stored procedure, use the DROP PROCEDURE statement:

   DROP PROCEDURE IF EXISTS process_transaction;
   

   Key Points

   • Encapsulation: Stored procedures encapsulate SQL logic, making it reusable and easier to manage.
   • Performance: They can improve performance by reducing the amount of data transferred between the database and application.
   • Error Handling: Proper error handling ensures that your database operations remain reliable and predictable.

---

Feel free to adjust the examples to better fit your specific use cases or database schema!