PostgreSQL - CREATE PROCEDURE

PostgreSQL CREATE PROCEDURE allows developers to define stored procedures that apply complex business logic, transaction handling and multiple SQL statements in a reusable manner. Unlike functions, PostgreSQL stored procedures can manage transactions with commands like COMMIT and ROLLBACK.

Understanding the PostgreSQL procedure syntax and how to efficiently call stored procedures is crucial for simplifying repetitive tasks and improving database performance.

PostgreSQL CREATE PROCEDURE

In PostgreSQL, a stored procedure is a set of SQL statements stored on the server that can be invoked to perform operations.

Procedures are designed to implement logic and make database operations more efficient especially when repetitive tasks or transaction handling is involved.

The CREATE PROCEDURE statement allows developers to define these procedures which can execute without returning values, unlike functions that must return values.

Syntax

The following illustrates the basic syntax of the CREATE PROCEDURE statement:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variable declarations (optional)
BEGIN
    -- Procedure body (SQL statements)
END;
$$;

Parameters:

• procedure_name: The name of the stored procedure.
  
• parameter_list: The parameters for the stored procedure, which can have IN and INOUT modes. OUT mode is not supported.
  
• LANGUAGE plpgsql: Specifies the procedural language. Other languages like SQL and C can also be used.
  
• $$: Dollar-quoted string constant syntax to define the body of the stored procedure.

Creating a Basic Stored Procedure

Let's start with a basic example where we create a procedure that inserts a record into an employees table.

Example:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE PROCEDURE add_employee(emp_name VARCHAR, emp_age INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, age) VALUES (emp_name, emp_age);
END;
$$;

Explanation:

• The add_employee procedure inserts a new employee into the employees table by taking the employee's name and age as parameters.

Calling the procedure:

CALL add_employee('John Doe', 25);

This will insert a new employee record with the name "John Doe" and age 25.

Procedure with Input Parameters

Stored procedures in PostgreSQL can accept parameters that influence the execution of the procedure. These parameters can be of type IN (input) or INOUT (input-output). Here's an example of a procedure with parameters:

Example:

CREATE PROCEDURE update_employee_age(emp_id INT, new_age INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees SET age = new_age WHERE id = emp_id;
END;
$$;

Calling the procedure:

CALL update_employee_age(1, 30);

This procedure updates the age of the employee with ID 1 to 30.

Transaction Handling in Procedures

One of the key advantages of using procedures is the ability to manage transactions. We can include BEGIN, COMMIT, and ROLLBACK statements inside procedures to handle transaction logic.

Example: Transferring money between accounts

Consider a scenario where we need to transfer money between two accounts. The transaction must either fully complete or roll back if an error occurs.

CREATE PROCEDURE transfer_money(sender INT, receiver INT, amount DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Subtract the amount from the sender's account
    UPDATE accounts SET balance = balance - amount WHERE id = sender;

    -- Add the amount to the receiver's account
    UPDATE accounts SET balance = balance + amount WHERE id = receiver;

    -- Commit the transaction
    COMMIT;
END;
$$;

Calling the procedure:

CALL transfer_money(1, 2, 1000);

This will transfer $1,000 from account 1 to account 2. The COMMIT ensures that the transaction is completed successfully.

Calling a Stored Procedure

To execute a stored procedure in PostgreSQL, the CALL statement is used. The syntax for calling a procedure is straightforward:

CALL procedure_name(argument_list);

Example:

CALL add_employee('Alice', 30);

This will execute the add_employee procedure and insert a new record into the employees table.

Important Points about CREATE PROCEDURE in PostgreSQL

• The CREATE PROCEDURE statement is used to define new stored procedures in PostgreSQL.
  
• Unlike user-defined functions, stored procedures in PostgreSQL can manage transaction.
  
• Stored procedures are executed using the CALL statement, followed by the procedure name and any necessary arguments.
  
• Stored procedures in PostgreSQL can accept parameters with IN and INOUT modes. IN parameters are used to pass values to the procedure, while INOUT parameters can pass values to and return values from the procedure.

Conclusion

Overall, PostgreSQL stored procedures into your database management allows for efficient execution of complex operations, reducing server load and improving reusability. By mastering the PostgreSQL procedure syntax and the process of calling stored procedures, developers can optimize transaction handling and streamline their database logic. This functionality not only enhances performance but also offers a powerful tool for maintaining data integrity in critical operations.