Creating_functions

Chapter 27: Creating Functions

Writing Custom Functions in PostgreSQL

---

27.1 Introduction to PostgreSQL Functions

PostgreSQL functions (also called stored procedures) allow you to encapsulate logic and business rules in the database.

                    Function Types in PostgreSQL
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      PostgreSQL Function Languages                   │
    │                                                                      │
    │   SQL Functions:                                                    │
    │   ───────────────                                                   │
    │   • Written in SQL                                                  │
    │   • Fast and simple                                                │
    │   • Limited procedural logic                                       │
    │                                                                      │
    │   PL/pgSQL:                                                        │
    │   ─────────                                                        │
    │   • PostgreSQL's procedural language                               │
    │   • Full control structures                                        │
    │   • Most commonly used                                             │
    │                                                                      │
    │   Other Languages:                                                  │
    │   ───────────────                                                  │
    │   • PL/Python, PL/Perl, PL/Tcl                                    │
    │   • C libraries                                                    │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

Basic Function Creation

-- Create a simple SQL function
CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
    SELECT COUNT(*) FROM users;
$$ LANGUAGE sql;

-- Execute the function
SELECT get_user_count();

-- Function with parameters
CREATE FUNCTION get_user_by_id(user_id INTEGER) RETURNS TABLE(
    id INTEGER,
    username VARCHAR,
    email VARCHAR
) AS $$
    SELECT user_id, username, email
    FROM users
    WHERE users.user_id = get_user_by_id.user_id;
$$ LANGUAGE sql;

-- Using the function
SELECT * FROM get_user_by_id(5);

---

27.2 SQL Functions

-- Simple scalar function
CREATE FUNCTION calculate_total(price DECIMAL, quantity INTEGER)
RETURNS DECIMAL AS $$
    SELECT price * quantity;
$$ LANGUAGE sql;

-- Function returning a set
CREATE FUNCTION get_active_users() RETURNS SETOF users AS $$
    SELECT * FROM users WHERE status = 'active';
$$ LANGUAGE sql;

-- Function with multiple statements
CREATE FUNCTION get_user_summary() RETURNS TABLE(
    total_users INTEGER,
    active_users INTEGER,
    inactive_users INTEGER
) AS $$
    SELECT
        COUNT(*)::INTEGER,
        COUNT(*) FILTER (WHERE status = 'active')::INTEGER,
        COUNT(*) FILTER (WHERE status = 'inactive')::INTEGER
    FROM users;
$$ LANGUAGE sql;

-- Function returning a single row
CREATE FUNCTION get_user_info(user_id INTEGER) RETURNS users AS $$
    SELECT * FROM users WHERE user_id = $1;
$$ LANGUAGE sql;

---

27.3 PL/pgSQL Functions

-- Basic PL/pgSQL function
CREATE FUNCTION add_user(
    p_username VARCHAR,
    p_email VARCHAR
) RETURNS INTEGER AS $$
DECLARE
    new_user_id INTEGER;
BEGIN
    INSERT INTO users (username, email)
    VALUES (p_username, p_email)
    RETURNING user_id INTO new_user_id;

    RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;

-- Using the function
SELECT add_user('john_doe', 'john@example.com');

-- Function with multiple parameters and return type
CREATE FUNCTION update_user_email(
    p_user_id INTEGER,
    p_new_email VARCHAR
) RETURNS BOOLEAN AS $$
DECLARE
    rows_affected INTEGER;
BEGIN
    UPDATE users
    SET email = p_new_email
    WHERE user_id = p_user_id;

    GET DIAGNOSTICS rows_affected = ROW_COUNT;

    IF rows_affected > 0 THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

---

27.4 Function Overloading

PostgreSQL supports function overloading - multiple functions with the same name but different parameters.

-- Overloaded functions
CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
    SELECT COUNT(*) FROM users;
$$ LANGUAGE sql;

CREATE FUNCTION get_user_count(p_status VARCHAR) RETURNS INTEGER AS $$
    SELECT COUNT(*) FROM users WHERE status = p_status;
$$ LANGUAGE sql;

-- Call with no parameters
SELECT get_user_count();

-- Call with status parameter
SELECT get_user_count('active');

-- Function with different parameter types
CREATE FUNCTION process_id(id INTEGER) RETURNS TEXT AS $$
    SELECT 'Integer: ' || id;
$$ LANGUAGE sql;

CREATE FUNCTION process_id(id VARCHAR) RETURNS TEXT AS $$
    SELECT 'Varchar: ' || id;
$$ LANGUAGE sql;

---

27.5 Using Arrays and Composite Types

-- Function using arrays
CREATE FUNCTION get_user_emails(p_status VARCHAR DEFAULT 'active')
RETURNS TEXT[] AS $$
    SELECT ARRAY_AGG(email)
    FROM users
    WHERE status = p_status;
$$ LANGUAGE sql;

-- Function returning array
SELECT get_user_emails();

-- Function with array parameter
CREATE FUNCTION find_users_by_emails(p_emails TEXT[]) RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR,
    email VARCHAR
) AS $$
    SELECT user_id, username, email
    FROM users
    WHERE email = ANY(p_emails);
$$ LANGUAGE sql;

SELECT * FROM find_users_by_emails(ARRAY['john@example.com', 'jane@example.com']);

-- Function using composite types
CREATE FUNCTION get_user_record(user_id INTEGER) RETURNS users AS $$
    SELECT * FROM users WHERE user_id = $1;
$$ LANGUAGE sql;

-- Using the composite result
SELECT (get_user_record(1)).username;

---

27.6 Default Parameters and Variadic Functions

-- Function with default parameters
CREATE FUNCTION create_order(
    p_user_id INTEGER,
    p_product_id INTEGER,
    p_quantity INTEGER DEFAULT 1,
    p_status VARCHAR DEFAULT 'pending'
) RETURNS INTEGER AS $$
DECLARE
    order_id INTEGER;
BEGIN
    INSERT INTO orders (user_id, product_id, quantity, status)
    VALUES (p_user_id, p_product_id, p_quantity, p_status)
    RETURNING order_id INTO order_id;

    RETURN order_id;
END;
$$ LANGUAGE plpgsql;

-- Various ways to call
SELECT create_order(1, 100);  -- uses defaults
SELECT create_order(1, 100, 2);  -- custom quantity
SELECT create_order(1, 100, 2, 'completed');  -- all custom

-- Variadic function (accepts variable number of arguments)
CREATE FUNCTION sum_values(VARIADIC nums INTEGER[]) RETURNS INTEGER AS $$
    SELECT SUM(unnest) FROM unnest(nums);
$$ LANGUAGE sql;

SELECT sum_values(1, 2, 3, 4, 5);
SELECT sum_values(VARIADIC ARRAY[1, 2, 3, 4, 5]);

---

27.7 Transaction Control in Functions

-- Function with explicit transaction control
CREATE FUNCTION transfer_funds(
    p_from_user INTEGER,
    p_to_user INTEGER,
    p_amount DECIMAL
) RETURNS BOOLEAN AS $$
BEGIN
    -- Check if from_user has enough balance
    IF (SELECT balance FROM accounts WHERE user_id = p_from_user) < p_amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Deduct from source
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE user_id = p_from_user;

    -- Add to destination
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE user_id = p_to_user;

    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Procedure-like function (PostgreSQL 11+)
CREATE PROCEDURE transfer_funds_proc(
    p_from_user INTEGER,
    p_to_user INTEGER,
    p_amount DECIMAL
) AS $$
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE user_id = p_from_user;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE user_id = p_to_user;

    COMMIT;
END;
$$ LANGUAGE plpgsql;

CALL transfer_funds_proc(1, 2, 100.00);

---

27.8 Best Practices

    Function Development Best Practices
    ========================================================================

    ✓ DO:
    ───────────────
    • Use proper naming conventions
    • Add comments and documentation
    • Use appropriate return types
    • Handle errors gracefully
    • Use VOLATILE, STABLE, or IMMUTABLE correctly
    • Consider security (SQL injection prevention)

    ✗ DON'T:
    ───────────────
    • Don't use SELECT * in production
    • Don't ignore NULL handling
    • Don't create functions for simple operations
    • Don't forget about performance

    Function Characteristics:
    ───────────────────────
    • VOLATILE - can modify data, results may vary
    • STABLE - does not modify data, results consistent within transaction
    • IMMUTABLE - no side effects, always same result for same input
*/

-- Mark function as immutable for optimizer benefits
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER) RETURNS INTEGER
AS $$ SELECT a + b; $$
LANGUAGE sql
IMMUTABLE;

---

27.9 Example: Complete Business Logic Function

-- Complex function with full business logic
CREATE FUNCTION calculate_order_total(
    p_order_id INTEGER,
    p_apply_discount BOOLEAN DEFAULT TRUE
) RETURNS TABLE(
    subtotal DECIMAL,
    discount DECIMAL,
    tax DECIMAL,
    total DECIMAL
) AS $$
DECLARE
    v_subtotal DECIMAL;
    v_discount DECIMAL := 0;
    v_tax_rate DECIMAL := 0.08;
    v_total DECIMAL;
BEGIN
    -- Calculate subtotal from order items
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    INTO v_subtotal
    FROM order_items
    WHERE order_id = p_order_id;

    -- Apply discount if enabled
    IF p_apply_discount THEN
        IF v_subtotal > 1000 THEN
            v_discount := v_subtotal * 0.10;  -- 10% discount
        ELSIF v_subtotal > 500 THEN
            v_discount := v_subtotal * 0.05;  -- 5% discount
        END IF;
    END IF;

    -- Calculate total with tax
    v_total := (v_subtotal - v_discount) * (1 + v_tax_rate);

    -- Return results
    RETURN QUERY SELECT v_subtotal, v_discount, v_subtotal * v_tax_rate, v_total;
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT * FROM calculate_order_total(1);

---

Summary

Function Type	Use Case
SQL Function	Simple data retrieval
PL/pgSQL	Complex business logic
IMMUTABLE	Pure functions, optimization
STABLE	Functions that don’t modify data

---

Next: Chapter 28: Stored Procedures