Views and Functions

Views and Functions in PostgreSQL

As your database grows, you will repeatedly write the same complex JOIN queries, the same aggregations, and the same calculations. PostgreSQL provides two tools for encapsulating this logic inside the database itself: views and functions.

Views let you save a query as a named object and query it like a table. Functions let you encapsulate procedural logic, accept parameters, and return results. Both reduce duplication, improve consistency, and allow the database to enforce business rules independently of the application layer.

Views — Named Saved Queries

A view is a stored SELECT statement with a name. Querying a view is identical to querying a table:

-- Create the view
CREATE VIEW active_products AS
SELECT product_id, name, category, price, stock
FROM products
WHERE is_active = TRUE AND stock > 0;

-- Query it like a table
SELECT * FROM active_products WHERE category = 'electronics';
SELECT COUNT(*) FROM active_products;
SELECT name FROM active_products WHERE price < 50 ORDER BY price;

Behind the scenes, PostgreSQL substitutes the view definition into the query. The view is not a copy of the data — it always reads from the underlying tables.

Creating and Replacing Views

-- Create a new view
CREATE VIEW customer_summary AS
SELECT
    customer_id,
    full_name,
    email,
    COUNT(order_id) AS total_orders,
    created_at
FROM customers c
LEFT JOIN orders o USING (customer_id)
GROUP BY customer_id, full_name, email, created_at;

-- Replace an existing view (PostgreSQL extension — preserves dependencies)
CREATE OR REPLACE VIEW customer_summary AS
SELECT
    customer_id,
    full_name,
    email,
    COUNT(order_id)     AS total_orders,
    SUM(total_amount)   AS lifetime_value,
    created_at
FROM customers c
LEFT JOIN orders o USING (customer_id)
GROUP BY customer_id, full_name, email, created_at;

-- Drop a view
DROP VIEW IF EXISTS customer_summary;

CREATE OR REPLACE VIEW updates the view definition without requiring you to drop and recreate it. This preserves grants and dependencies.

Updatable Views

Simple views (no aggregation, no DISTINCT, no GROUP BY, no subqueries) are automatically updatable in PostgreSQL — you can INSERT, UPDATE, and DELETE through them:

CREATE VIEW active_users AS
SELECT user_id, email, full_name
FROM users
WHERE is_active = TRUE;

-- This works because the view is simple and the WHERE column is in the base table
UPDATE active_users SET full_name = 'Jane Doe' WHERE user_id = 5;

For complex views that are not automatically updatable, you can define INSTEAD OF triggers to handle writes manually.

Materialized Views — Cached Query Results

A materialized view is like a regular view except that the query result is actually stored on disk. This makes reads extremely fast because no computation is done at query time — but the data is only as fresh as the last refresh.

-- Create a materialized view for daily analytics
CREATE MATERIALIZED VIEW product_stats AS
SELECT
    p.category,
    COUNT(DISTINCT oi.order_id)               AS times_ordered,
    SUM(oi.quantity)                          AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS gross_revenue
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders      o  ON o.order_id    = oi.order_id AND o.status = 'completed'
GROUP BY p.category;

-- Add an index to the materialized view for fast lookups
CREATE INDEX ON product_stats (category);

-- Refresh the data (replaces all content)
REFRESH MATERIALIZED VIEW product_stats;

-- Refresh without locking reads (requires a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;

CONCURRENTLY lets existing readers continue reading the old data while the new data is being computed. It requires a unique index on the materialized view.

Materialized views are ideal for:

• Analytics dashboards queried frequently but updated infrequently
• Expensive aggregation queries over millions of rows
• Reporting tables that can tolerate hourly or daily staleness

SQL Functions

A SQL function encapsulates a single SQL expression or query and can be called from any SQL context:

-- Simple function: calculate discounted price
CREATE OR REPLACE FUNCTION discounted_price(
    base_price NUMERIC,
    discount_pct NUMERIC DEFAULT 10
)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ROUND(base_price * (1 - discount_pct / 100.0), 2)
$$;

-- Use in queries
SELECT name, price, discounted_price(price, 15) AS sale_price
FROM products
WHERE category = 'electronics';

The IMMUTABLE marker tells PostgreSQL that the function always returns the same result for the same inputs and does not read the database. This allows the planner to evaluate the function once and cache the result — a significant optimization.

Other volatility categories:

• STABLE: same inputs return same results within a single query (can read the database)
• VOLATILE (default): results can change even within a single query

PL/pgSQL Functions

PL/pgSQL is PostgreSQL's procedural language. It adds variables, conditionals, loops, and exception handling to SQL:

CREATE OR REPLACE FUNCTION apply_discount(
    p_customer_id INTEGER,
    p_discount_pct NUMERIC
)
RETURNS TABLE(product_id INTEGER, name VARCHAR, original_price NUMERIC, sale_price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Validate input
    IF p_discount_pct < 0 OR p_discount_pct > 100 THEN
        RAISE EXCEPTION 'Discount percentage must be between 0 and 100, got %',
                        p_discount_pct;
    END IF;

    RETURN QUERY
    SELECT
        p.product_id,
        p.name,
        p.price                                         AS original_price,
        ROUND(p.price * (1 - p_discount_pct / 100), 2) AS sale_price
    FROM products p
    JOIN (
        SELECT DISTINCT oi.product_id
        FROM order_items oi
        JOIN orders o ON o.order_id = oi.order_id
        WHERE o.customer_id = p_customer_id
          AND o.status = 'completed'
    ) purchased ON purchased.product_id = p.product_id;
END;
$$;

-- Call it
SELECT * FROM apply_discount(5, 20);

PL/pgSQL Control Flow

CREATE OR REPLACE FUNCTION classify_order(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_tier TEXT;
BEGIN
    IF p_amount >= 1000 THEN
        v_tier := 'enterprise';
    ELSIF p_amount >= 500 THEN
        v_tier := 'standard';
    ELSIF p_amount >= 100 THEN
        v_tier := 'small';
    ELSE
        v_tier := 'micro';
    END IF;

    RETURN v_tier;
END;
$$;

Exception Handling

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'Division by zero: cannot divide % by 0', a;
    END IF;
    RETURN a / b;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Unexpected error in safe_divide: %', SQLERRM;
        RETURN NULL;
END;
$$;

Dropping Functions and Views

-- Drop a view
DROP VIEW IF EXISTS order_summary;
DROP MATERIALIZED VIEW IF EXISTS product_stats;

-- Drop a function (specify parameter types to disambiguate overloaded functions)
DROP FUNCTION IF EXISTS get_order_total(INTEGER);
DROP FUNCTION IF EXISTS discounted_price(NUMERIC, NUMERIC);

Views and functions are the database's equivalent of encapsulation and abstraction in application code. They let you define your query logic once and reuse it everywhere, centralize business rules, and simplify complex operations for application developers. The next lesson moves to database design: normalization.