PostgreSQL Functions: Writing Your First Custom Functions and Procedures

Table of Contents

1. What Are PostgreSQL Functions and Procedures?
   • Key Differences Between Functions and Procedures
2. Prerequisites: Setting Up Your Environment
   • Sample Database Setup
3. Writing Your First PostgreSQL Function
   • Basic Syntax
   • Example 1: Simple Data Retrieval
   • Example 2: Parameterized Calculations
4. Exploring PostgreSQL Procedures
   • Basic Syntax
   • Example: Transactional Data Modification
5. Advanced Concepts
   • Exception Handling
   • Volatility and Optimization Hints
   • Table-Valued Functions
6. When to Use Functions vs. Procedures
7. Best Practices for Writing Custom Logic
8. Troubleshooting Common Issues
9. References

What Are PostgreSQL Functions and Procedures?

Definitions

• Functions: Reusable blocks of code that accept input parameters, perform computations or queries, and return a single value or a set of values. They are invoked using SELECT and cannot contain transaction control statements (e.g., COMMIT, ROLLBACK).
• Procedures: Introduced in PostgreSQL 11, procedures are similar to functions but are designed for executing actions (e.g., data modifications) rather than returning values. They support transaction control and are invoked with CALL.

Key Differences

Prerequisites: Setting Up Your Environment

To follow along, ensure you have:

• PostgreSQL 11+ installed (procedures require v11+).
• A SQL client (e.g., psql, pgAdmin, or DBeaver).

Sample Database Setup

Let’s create a sample company database with an employees table to use in examples:

-- Create database
CREATE DATABASE company;
\c company; -- Connect to the database (in psql)

-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary NUMERIC(10,2) NOT NULL,
    hire_date DATE NOT NULL
);

-- Insert sample data
INSERT INTO employees (id, name, department, salary, hire_date) VALUES
(1, 'Alice Smith', 'Engineering', 90000, '2020-01-15'),
(2, 'Bob Johnson', 'Marketing', 75000, '2019-03-22'),
(3, 'Charlie Brown', 'Engineering', 85000, '2021-05-30');

Writing Your First PostgreSQL Function

Basic Syntax

The CREATE FUNCTION statement defines a function. Here’s the general structure:

CREATE OR REPLACE FUNCTION function_name(parameter1 type, parameter2 type, ...)
RETURNS return_type
LANGUAGE plpgsql -- Or SQL, Python, etc.
AS $$
DECLARE
    -- Variable declarations (optional)
BEGIN
    -- Logic here
    RETURN result;
END;
$$;

Example 1: Simple Data Retrieval

Let’s write a function to count employees in a specific department.

Step 1: Define the function

CREATE OR REPLACE FUNCTION count_employees_by_dept(dept_name TEXT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    emp_count INT; -- Variable to store the result
BEGIN
    -- Query to count employees in the department
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department = dept_name;

    RETURN emp_count; -- Return the count
END;
$$;

Step 2: Invoke the function

SELECT count_employees_by_dept('Engineering'); -- Returns 2 (from our sample data)

Example 2: Parameterized Calculations

Next, let’s create a function to calculate an employee’s tenure (in years) based on their hire date.

CREATE OR REPLACE FUNCTION calculate_tenure(hire_date DATE)
RETURNS NUMERIC(5,2)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Compute tenure using EXTRACT and CURRENT_DATE
    RETURN EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date));
END;
$$;

Invoke it with employee data:

SELECT name, calculate_tenure(hire_date) AS tenure_years
FROM employees;

Output:

   name    | tenure_years
-----------+--------------
 Alice Smith |         4.00
 Bob Johnson |         5.00
 Charlie Brown |         3.00

Exploring PostgreSQL Procedures

Procedures excel at transactional operations like bulk updates or data migrations. Let’s create a procedure to give employees a salary raise, with transaction control.

Basic Syntax

CREATE OR REPLACE PROCEDURE procedure_name(parameter1 type, ...)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Logic with possible transaction control
END;
$$;

Example: Transactional Salary Update

Let’s write a procedure to update an employee’s salary by a percentage, with a check to avoid negative raises.

CREATE OR REPLACE PROCEDURE update_salary(emp_id INT, raise_pct NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    current_salary NUMERIC(10,2);
BEGIN
    -- Validate input: raise percentage must be non-negative
    IF raise_pct < 0 THEN
        RAISE EXCEPTION 'Raise percentage cannot be negative: %', raise_pct;
    END IF;

    -- Get current salary
    SELECT salary INTO current_salary
    FROM employees
    WHERE id = emp_id;

    -- Update salary (with transaction control)
    UPDATE employees
    SET salary = current_salary * (1 + raise_pct / 100)
    WHERE id = emp_id;

    -- Optional: Explicit COMMIT (auto-committed by default in procedures)
    -- COMMIT;
END;
$$;

Invoke the procedure:

CALL update_salary(1, 5); -- Give Alice a 5% raise

Verify the update:

SELECT name, salary FROM employees WHERE id = 1; -- Salary increases from 90000 to 94500

Handle errors: If we pass a negative raise, PostgreSQL throws an error:

CALL update_salary(1, -10); -- Throws: "Raise percentage cannot be negative: -10"

Advanced Concepts

Exception Handling

Use the EXCEPTION block to catch and handle errors (e.g., division by zero, missing data).

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero!';
        RETURN NULL; -- Return NULL instead of failing
END;
$$;

Test it:

SELECT safe_divide(10, 2); -- Returns 5
SELECT safe_divide(10, 0); -- Prints "Cannot divide by zero!" and returns NULL

Volatility and Optimization Hints

PostgreSQL uses volatility hints to optimize function execution. Declare functions as:

• IMMUTABLE: Always returns the same result for the same inputs (e.g., calculate_tenure).
• STABLE: Results don’t change within a transaction (e.g., functions using CURRENT_DATE).
• VOLATILE: Results can change on every call (default; e.g., random()).

Example: Mark calculate_tenure as STABLE (since CURRENT_DATE doesn’t change mid-transaction):

CREATE OR REPLACE FUNCTION calculate_tenure(hire_date DATE)
RETURNS NUMERIC(5,2)
LANGUAGE plpgsql
STABLE -- Hint for the query planner
AS $$ ... $$;

Table-Valued Functions

Return multiple rows/columns with RETURNS TABLE or RETURNS SETOF.

CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_name TEXT)
RETURNS TABLE(id INT, name TEXT, salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    WHERE e.department = dept_name;
END;
$$;

Invoke as a table:

SELECT * FROM get_employees_by_dept('Marketing');

When to Use Functions vs. Procedures

Best Practices for Writing Custom Logic

1. Naming Conventions: Use descriptive names (e.g., calculate_tenure instead of fn1).
2. Avoid Side Effects in Functions: Functions should not modify data (use procedures for that).
3. Document with Comments: Use COMMENT ON to explain logic:

   COMMENT ON FUNCTION count_employees_by_dept(TEXT) IS 'Counts employees in a given department';

4. Test Thoroughly: Validate edge cases (e.g., NULL inputs, invalid parameters).
5. Optimize Volatility: Mark functions as IMMUTABLE/STABLE to improve performance.

Troubleshooting Common Issues

• “Return type mismatch”: Ensure the RETURN value matches the declared RETURNS type.
• “Procedure not found”: Use CALL (not SELECT) to invoke procedures.
• “Permission denied”: Ensure your user has CREATE privileges on the database.
• View Existing Functions/Procedures: Use \df (functions) or \dp (procedures) in psql, or query the system catalog:

  SELECT proname, proargtypes, prorettype
  FROM pg_proc
  WHERE proname LIKE 'count_employees%';

References

• PostgreSQL Official Documentation: Functions and Procedures
• PostgreSQL PL/pgSQL Guide
• PostgreSQL Performance Tuning for Functions
• Book: PostgreSQL 11 Administration Cookbook by Simon Riggs and Gianni Ciolli

By mastering PostgreSQL functions and procedures, you’ll unlock new levels of flexibility and efficiency in your database workflows. Start small, experiment with the examples above, and gradually incorporate custom logic into your projects!