The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Stored Procedures and Functions

Next ►
►

Stored procedures and functions are essential components in PostgreSQL that allow you to encapsulate complex logic within the database. They help in improving performance, maintaining consistency, and reusing code. This section will cover the basics of creating and using stored procedures and functions in PostgreSQL.

Key Concepts

  1. Stored Procedures:

    • A stored procedure is a set of SQL statements that can be executed as a single unit.
    • They can perform operations such as data manipulation, control flow, and error handling.
    • Stored procedures do not return a value.

  2. Functions:

    • Functions are similar to stored procedures but are designed to return a value.
    • They can be used in SQL queries, allowing for more flexible and reusable code.

Creating Stored Procedures

Syntax

CREATE PROCEDURE procedure_name (parameter_list)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Procedure logic here
END;
$$;

Example

Let's create a stored procedure that inserts a new record into a table called employees.

CREATE PROCEDURE add_employee(
    emp_name VARCHAR,
    emp_position VARCHAR,
    emp_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, position, salary)
    VALUES (emp_name, emp_position, emp_salary);
END;
$$;

Executing Stored Procedures

To execute a stored procedure, use the CALL statement.

CALL add_employee('John Doe', 'Software Engineer', 75000);

Creating Functions

Syntax

CREATE FUNCTION function_name (parameter_list)
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
    -- Function logic here
    RETURN some_value;
END;
$$;

Example

Let's create a function that calculates the annual salary of an employee based on their monthly salary.

CREATE FUNCTION calculate_annual_salary(monthly_salary NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN monthly_salary * 12;
END;
$$;

Using Functions

Functions can be used in SQL queries. For example:

SELECT calculate_annual_salary(5000);

Practical Exercises

Exercise 1: Create a Stored Procedure

Task: Create a stored procedure named update_employee_salary that updates the salary of an employee based on their ID.

Solution:

CREATE PROCEDURE update_employee_salary(
    emp_id INT,
    new_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
END;
$$;

Execution:

CALL update_employee_salary(1, 80000);

Exercise 2: Create a Function

Task: Create a function named get_employee_position that returns the position of an employee based on their ID.

Solution:

CREATE FUNCTION get_employee_position(emp_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    emp_position VARCHAR;
BEGIN
    SELECT position INTO emp_position
    FROM employees
    WHERE id = emp_id;
    
    RETURN emp_position;
END;
$$;

Usage:

SELECT get_employee_position(1);

Common Mistakes and Tips

  • Syntax Errors: Ensure that the syntax is correct, especially the use of $$ to delimit the procedure or function body.
  • Parameter Types: Make sure the parameter types match the data types in the table.
  • Error Handling: Consider adding error handling within your procedures and functions to manage exceptions gracefully.
  • Testing: Always test your stored procedures and functions with different inputs to ensure they work as expected.

Conclusion

In this section, you learned how to create and use stored procedures and functions in PostgreSQL. These tools are powerful for encapsulating complex logic, improving performance, and maintaining consistency in your database operations. Practice creating and using stored procedures and functions to become proficient in managing database logic within PostgreSQL.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved