Stored Procedures and Functions

80 min

PostgreSQL supports stored procedures and functions that enable you to encapsulate business logic in the database. Functions are callable routines that return values, while procedures (introduced in PostgreSQL 11) can perform actions without returning values. Both enable code reuse, improve performance by reducing network round-trips, and centralize business logic. Understanding when and how to use stored procedures and functions is essential for building maintainable database applications.

PL/pgSQL is PostgreSQL's native procedural language, similar to Oracle's PL/SQL. PL/pgSQL functions can contain variables, control structures (IF/ELSE, loops), exception handling, and SQL statements. Functions can return scalar values, tables, or sets. PL/pgSQL is compiled and cached, providing good performance. It's the most common language for PostgreSQL stored procedures and functions, offering a balance of power and familiarity for SQL developers.

PostgreSQL supports multiple procedural languages including PL/Python, PL/JavaScript, PL/Perl, and PL/Tcl. These languages enable you to write functions in languages you're already familiar with, leveraging their libraries and capabilities. PL/Python is particularly popular for data processing and integration with Python ecosystems. Each language has its strengths: Python for data science, JavaScript for web integration, etc.

Functions can return various types: scalar values (single values), composite types (rows), tables (multiple rows), or sets. Table-returning functions enable you to create functions that behave like tables, useful for complex queries or data transformations. Functions can also return arrays, JSON, or custom types. Understanding return types enables you to design functions that integrate seamlessly with SQL queries.

Stored procedures (introduced in PostgreSQL 11) differ from functions in that they don't return values and can't be used in SQL expressions. Procedures are useful for operations that perform actions (like data modifications) without needing return values. Procedures support transaction control (COMMIT/ROLLBACK) within their execution, enabling complex multi-step operations. Functions are still preferred for most use cases, but procedures fill specific needs.

Best practices for stored procedures and functions include keeping them focused on single responsibilities, using appropriate parameter types, handling errors gracefully, and documenting their purpose and usage. Functions should be idempotent when possible and should not have side effects beyond their intended purpose. Understanding when to use database functions vs application code helps you make appropriate architectural decisions.

Key Concepts

Stored procedures and functions encapsulate business logic in the database.
PL/pgSQL is PostgreSQL's native procedural language.
PostgreSQL supports multiple procedural languages (Python, JavaScript, etc.).
Functions can return scalar values, tables, or complex objects.
Stored procedures perform actions without returning values.

Learning Objectives

Master

Creating PL/pgSQL functions and procedures
Understanding function parameters and return types
Implementing error handling in stored procedures
Using functions in SQL queries and applications

Develop

Understanding database programming patterns
Designing maintainable stored procedures
Choosing between database functions and application code

Tips

Use CREATE FUNCTION for reusable logic, CREATE PROCEDURE for actions.
Handle errors: BEGIN ... EXCEPTION WHEN ... THEN ... END;
Use RETURNS TABLE for functions that return multiple rows.
Test functions thoroughly: functions run in database context with different error handling.

Common Pitfalls

Putting too much business logic in database, reducing application flexibility.
Not handling errors, causing function failures to break transactions.
Creating functions with side effects, causing unexpected behavior.
Not documenting functions, making maintenance difficult.

Summary

Stored procedures and functions encapsulate database logic.
PL/pgSQL is the primary language for PostgreSQL functions.
Functions can return various types and integrate with SQL queries.
Understanding when to use functions vs application code is important.

Exercise

Create stored procedures and functions for common database operations.

-- Create a function to calculate order statistics
CREATE OR REPLACE FUNCTION get_customer_order_stats(p_customer_id INTEGER)
RETURNS TABLE(
    total_orders BIGINT,
    total_spent DECIMAL(10,2),
    avg_order_amount DECIMAL(10,2),
    last_order_date DATE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*)::BIGINT,
        COALESCE(SUM(total_amount), 0),
        COALESCE(AVG(total_amount), 0),
        MAX(order_date)
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$ LANGUAGE plpgsql;

-- Create a procedure to update order status
CREATE OR REPLACE PROCEDURE update_order_status(
    p_order_id INTEGER,
    p_new_status VARCHAR(20)
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders 
    SET status = p_new_status
    WHERE id = p_order_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order with ID % not found', p_order_id;
    END IF;
    
    COMMIT;
END;
$$;

-- Use the function
SELECT * FROM get_customer_order_stats(123);

-- Use the procedure
CALL update_order_status(456, 'shipped');

Exercise Tips

Use RETURNS SETOF for functions returning multiple rows of same type.
Create functions with default parameters: FUNCTION func(param INT DEFAULT 10).
Use RAISE for debugging: RAISE NOTICE 'Value: %', variable;
Test functions: SELECT function_name(param); to verify behavior.

Stored Procedures and Functions