Stored Functions and Procedures in PostgreSQL

Stored functions and procedures are essential components of PostgreSQL that allow you to encapsulate and execute complex logic directly within the database. They improve modularity and code reusability, and they can significantly enhance performance by reducing the amount of data transferred between the database server and client applications.

2.1 Definition

A stored function is a set of SQL statements that can be stored in the database and executed when called. Unlike stored procedures, functions return a single value or a table.

2.2 Syntax

CREATE FUNCTION function_name(parameter_list) 
RETURNS return_type AS $$
BEGIN
    -- function body
END;
$$ LANGUAGE plpgsql;

2.3 Example

CREATE FUNCTION get_employee_count() 
RETURNS INTEGER AS $$
DECLARE
    emp_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
    RETURN emp_count;
END;
$$ LANGUAGE plpgsql;

3.1 Definition

A stored procedure is a set of SQL statements that perform a specific task. Procedures can perform actions but do not need to return a value.

3.2 Syntax

CREATE PROCEDURE procedure_name(parameter_list) 
AS $$
BEGIN
    -- procedure body
END;
$$ LANGUAGE plpgsql;

3.3 Example

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

Implementing stored functions and procedures effectively involves several best practices:

• Use meaningful names that describe the function's purpose.
• Keep functions short and focused on a single task.
• Document functions and procedures adequately with comments.
• Handle exceptions to avoid unexpected errors.