Views, Functions, and Stored Procedures

My journey from duplicated query nightmares to reusable database logic

---

Table of Contents

1. Introduction: The Copy-Paste Query Problem

2. Views: Named, Reusable Queries

3. Materialised Views: Cached Query Results

4. PostgreSQL Functions (User-Defined Functions)

5. Stored Procedures

6. Triggers: Automatic Database Reactions

7. PL/pgSQL: Writing Procedural Code in PostgreSQL

8. Common Patterns and Use Cases

9. Performance Considerations

10. Managing Database Code

11. What I Learned About Database Logic

12. Next Steps

---

Introduction: The Copy-Paste Query Problem

My blog admin dashboard needed the same "published posts with author and comment count" query in seven different places: homepage, author profile pages, category listing, search results, RSS feed generator, sitemap builder, and the admin panel itself.

I copied the query seven times. Within two weeks, I had seven slightly different versions. The homepage query had a bug fix that I'd applied to only three of the others. The admin panel was using LEFT JOIN where it needed INNER JOIN. One version was missing the status = 'published' filter entirely.

Sound familiar?

The solution was to move that shared logic into the database itself using views and functions. Now I define the query once. All seven places reference the view. Fix the view once, everyone benefits.

---

Views: Named, Reusable Queries

A view is a stored SELECT statement that behaves like a table. It doesn't store data—it runs the query each time it's accessed.

Creating a View

-- View: published posts with all related data
CREATE VIEW published_posts AS
SELECT
    p.id,
    p.title,
    p.content,
    p.views,
    p.published_at,
    a.id            AS author_id,
    a.name          AS author_name,
    a.email         AS author_email,
    COUNT(c.id)     AS comment_count,
    STRING_AGG(DISTINCT cat.name, ', ' ORDER BY cat.name) AS categories
FROM posts p
JOIN authors a          ON p.author_id = a.id
LEFT JOIN comments c    ON p.id = c.post_id
LEFT JOIN post_categories pc ON p.id = pc.post_id
LEFT JOIN categories cat ON pc.category_id = cat.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, p.content, p.views, p.published_at,
         a.id, a.name, a.email;

Now every part of the application uses this:

-- Simple, clean queries everywhere
SELECT * FROM published_posts ORDER BY published_at DESC LIMIT 10;
SELECT * FROM published_posts WHERE author_id = 5;
SELECT * FROM published_posts WHERE categories ILIKE '%postgresql%';

Modifying Views

-- Replace a view (PostgreSQL 9.4+)
CREATE OR REPLACE VIEW published_posts AS
SELECT
    -- Updated query
    p.id,
    p.title,
    -- ... additional columns ...
    p.slug          -- new column added
FROM posts p
-- ...
WHERE p.status = 'published';

-- Drop and recreate (for structural changes that OR REPLACE can't handle)
DROP VIEW IF EXISTS published_posts;
CREATE VIEW published_posts AS ...;

Updatable Views

Simple views (no aggregates, no JOINs, no DISTINCT, one base table) are automatically updatable:

CREATE VIEW active_authors AS
SELECT id, name, email, bio, created_at
FROM authors
WHERE deleted_at IS NULL;

-- These work directly on the view:
INSERT INTO active_authors (name, email) VALUES ('New Author', '[email protected]');
UPDATE active_authors SET bio = 'Updated bio' WHERE id = 5;
-- Rows where deleted_at IS NOT NULL are not visible or updatable through this view

Views with SECURITY DEFINER

-- A view that runs with the permissions of its creator
-- Useful for exposing limited data to restricted roles
CREATE VIEW public_posts
WITH (security_barrier = true)
AS
SELECT id, title, published_at, author_id
FROM posts
WHERE status = 'published';

---

Materialised Views: Cached Query Results

A materialised view stores the query results on disk. Perfect for expensive aggregations that don't need real-time accuracy:

-- Monthly statistics - expensive to calculate in real time
CREATE MATERIALIZED VIEW monthly_post_stats AS
SELECT
    DATE_TRUNC('month', published_at)   AS month,
    COUNT(*)                             AS post_count,
    SUM(views)                           AS total_views,
    AVG(views)                           AS avg_views,
    COUNT(DISTINCT author_id)            AS active_authors
FROM posts
WHERE status = 'published'
GROUP BY DATE_TRUNC('month', published_at)
ORDER BY month;

-- Create an index on the materialised view
CREATE INDEX idx_monthly_stats_month ON monthly_post_stats(month);

Refreshing Materialised Views

The data becomes stale immediately; you must refresh manually or via a schedule:

-- Refresh (blocks reads during refresh)
REFRESH MATERIALIZED VIEW monthly_post_stats;

-- Refresh concurrently (allows reads during refresh, needs unique index)
CREATE UNIQUE INDEX idx_monthly_stats_unique ON monthly_post_stats(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_post_stats;

Scheduling Refreshes

-- Using pg_cron extension (schedule refresh at midnight daily)
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'refresh-monthly-stats',       -- job name
    '0 0 * * *',                   -- cron expression: midnight every day
    'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_post_stats'
);

---

PostgreSQL Functions (User-Defined Functions)

Functions let you encapsulate logic, accept parameters, and return values. They're executed with SELECT.

Simple SQL Function

-- Calculate reading time for a post (words / 200 wpm)
CREATE OR REPLACE FUNCTION calculate_reading_time(content TEXT)
RETURNS INTEGER AS $$
    SELECT GREATEST(1, ROUND(
        ARRAY_LENGTH(STRING_TO_ARRAY(TRIM(content), ' '), 1) / 200.0
    )::INTEGER);
$$ LANGUAGE SQL IMMUTABLE;

-- Usage
SELECT title, calculate_reading_time(content) AS reading_minutes
FROM posts
WHERE status = 'published';

Function That Returns a Table

-- Get top posts for an author with engagement score
CREATE OR REPLACE FUNCTION get_top_posts_for_author(
    p_author_id   INTEGER,
    p_limit       INTEGER DEFAULT 5
)
RETURNS TABLE (
    post_id       INTEGER,
    title         TEXT,
    views         INTEGER,
    comment_count BIGINT,
    score         BIGINT
) AS $$
    SELECT
        p.id,
        p.title,
        p.views,
        COUNT(c.id),
        p.views::BIGINT + COUNT(c.id) * 10
    FROM posts p
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.author_id = p_author_id
      AND p.status = 'published'
    GROUP BY p.id, p.title, p.views
    ORDER BY p.views + COUNT(c.id) * 10 DESC
    LIMIT p_limit;
$$ LANGUAGE SQL STABLE;

-- Usage
SELECT * FROM get_top_posts_for_author(5);
SELECT * FROM get_top_posts_for_author(5, 10);

Function Volatility

Marker	Meaning	Can Be Cached?
IMMUTABLE	Same inputs always return same result; no DB access	Yes, aggressively
STABLE	Same inputs return same result within a transaction	Yes, within query
VOLATILE (default)	May return different results each call	Never

Use the most restrictive volatility that's accurate—it allows PostgreSQL to optimise better.

Functions with OUT Parameters

-- Function with multiple return values via OUT parameters
CREATE OR REPLACE FUNCTION get_post_stats(
    p_post_id     INTEGER,
    OUT view_count INTEGER,
    OUT comment_count INTEGER,
    OUT avg_rating NUMERIC
)
AS $$
BEGIN
    SELECT p.views INTO view_count
    FROM posts p WHERE p.id = p_post_id;

    SELECT COUNT(*) INTO comment_count
    FROM comments WHERE post_id = p_post_id;

    avg_rating := 4.2;  -- from a ratings table (simplified)
END;
$$ LANGUAGE plpgsql STABLE;

-- Usage
SELECT * FROM get_post_stats(42);

---

Stored Procedures

Stored procedures (PostgreSQL 11+) are similar to functions but:

• Called with CALL instead of SELECT

• Can manage their own transactions (COMMIT/ROLLBACK inside them)

• Cannot return values directly (use INOUT parameters instead)

-- Procedure to publish a post with full workflow
CREATE OR REPLACE PROCEDURE publish_post(
    p_post_id   INTEGER,
    INOUT result TEXT DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_author_id INTEGER;
    v_post_title TEXT;
BEGIN
    -- Get post details
    SELECT author_id, title INTO v_author_id, v_post_title
    FROM posts
    WHERE id = p_post_id AND status = 'draft';

    IF NOT FOUND THEN
        result := 'ERROR: Post not found or not in draft status';
        RETURN;
    END IF;

    -- Update post
    UPDATE posts
    SET status = 'published', published_at = NOW()
    WHERE id = p_post_id;

    -- Update author stats
    UPDATE authors
    SET published_post_count = published_post_count + 1
    WHERE id = v_author_id;

    -- Log activity
    INSERT INTO activity_log (action, entity_type, entity_id, created_at)
    VALUES ('post_published', 'post', p_post_id, NOW());

    result := 'SUCCESS: Published "' || v_post_title || '"';

    COMMIT;  -- Procedures can commit internally
END;
$$;

-- Usage
CALL publish_post(42);

-- With result output
DO $$
DECLARE msg TEXT;
BEGIN
    CALL publish_post(42, msg);
    RAISE NOTICE 'Result: %', msg;
END;
$$;

---

Triggers: Automatic Database Reactions

Triggers automatically execute a function when specific events occur on a table:

Types of Triggers

Auto-Update updated_at Timestamp

-- Trigger function to auto-set updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to posts table
ALTER TABLE posts ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ;

CREATE TRIGGER trg_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Now every UPDATE on posts automatically sets updated_at
UPDATE posts SET title = 'New Title' WHERE id = 42;
-- updated_at is automatically set to NOW()

Audit Log Trigger

-- Audit table to track all changes
CREATE TABLE post_audit_log (
    id          SERIAL PRIMARY KEY,
    post_id     INTEGER NOT NULL,
    action      TEXT NOT NULL,     -- 'INSERT', 'UPDATE', 'DELETE'
    old_data    JSONB,
    new_data    JSONB,
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger function
CREATE OR REPLACE FUNCTION log_post_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO post_audit_log (post_id, action, new_data)
        VALUES (NEW.id, 'INSERT', to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO post_audit_log (post_id, action, old_data, new_data)
        VALUES (NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO post_audit_log (post_id, action, old_data)
        VALUES (OLD.id, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger to posts
CREATE TRIGGER trg_audit_posts
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION log_post_changes();

Validation Trigger

-- Prevent publishing posts without a title and content
CREATE OR REPLACE FUNCTION validate_post_before_publish()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'published' AND OLD.status != 'published' THEN
        IF LENGTH(TRIM(COALESCE(NEW.title, ''))) < 5 THEN
            RAISE EXCEPTION 'Post title must be at least 5 characters to publish';
        END IF;
        IF LENGTH(TRIM(COALESCE(NEW.content, ''))) < 100 THEN
            RAISE EXCEPTION 'Post content must be at least 100 characters to publish';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_post_publish
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION validate_post_before_publish();

---

PL/pgSQL: Writing Procedural Code in PostgreSQL

PL/pgSQL is PostgreSQL's procedural language for writing complex database logic:

Variables and Control Flow

CREATE OR REPLACE FUNCTION describe_post_performance(p_post_id INTEGER)
RETURNS TEXT AS $$
DECLARE
    v_views      INTEGER;
    v_comments   INTEGER;
    v_score      INTEGER;
    v_label      TEXT;
BEGIN
    SELECT views INTO v_views FROM posts WHERE id = p_post_id;
    SELECT COUNT(*) INTO v_comments FROM comments WHERE post_id = p_post_id;

    v_score := v_views + v_comments * 10;

    IF v_score >= 10000 THEN
        v_label := 'Viral';
    ELSIF v_score >= 1000 THEN
        v_label := 'Popular';
    ELSIF v_score >= 100 THEN
        v_label := 'Growing';
    ELSE
        v_label := 'New';
    END IF;

    RETURN v_label || ' (score: ' || v_score::TEXT || ')';
END;
$$ LANGUAGE plpgsql STABLE;

SELECT title, describe_post_performance(id) AS performance
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 10;

Looping

-- Process and tag old untagged posts
CREATE OR REPLACE PROCEDURE tag_legacy_posts()
LANGUAGE plpgsql AS $$
DECLARE
    v_post RECORD;
    v_count INTEGER := 0;
BEGIN
    FOR v_post IN
        SELECT id, title, content
        FROM posts
        WHERE status = 'published'
          AND id NOT IN (SELECT DISTINCT post_id FROM post_tags)
        ORDER BY id
    LOOP
        -- Simple auto-tagging: tag as 'legacy'
        INSERT INTO post_tags (post_id, tag_id)
        SELECT v_post.id, id FROM tags WHERE name = 'legacy'
        ON CONFLICT DO NOTHING;

        v_count := v_count + 1;
    END LOOP;

    RAISE NOTICE 'Tagged % posts as legacy', v_count;
END;
$$;

CALL tag_legacy_posts();

Exception Handling

CREATE OR REPLACE FUNCTION safe_publish_post(p_post_id INTEGER)
RETURNS TEXT AS $$
BEGIN
    UPDATE posts
    SET status = 'published', published_at = NOW()
    WHERE id = p_post_id AND status = 'draft';

    IF NOT FOUND THEN
        RETURN 'Post not found or already published';
    END IF;

    RETURN 'Success';

EXCEPTION
    WHEN check_violation THEN
        RETURN 'Validation failed: ' || SQLERRM;
    WHEN foreign_key_violation THEN
        RETURN 'Referenced data not found: ' || SQLERRM;
    WHEN OTHERS THEN
        RETURN 'Unexpected error: ' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

---

Common Patterns and Use Cases

Soft Delete Pattern

-- Instead of deleting rows, mark them as deleted
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE VIEW active_posts AS
SELECT * FROM posts WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION soft_delete_post(p_post_id INTEGER)
RETURNS VOID AS $$
    UPDATE posts SET deleted_at = NOW() WHERE id = p_post_id;
$$ LANGUAGE SQL;

-- Usage
SELECT soft_delete_post(42);
SELECT * FROM active_posts;  -- deleted post not visible

Slug Generation

-- Auto-generate URL slugs from titles
CREATE OR REPLACE FUNCTION generate_slug(title TEXT)
RETURNS TEXT AS $$
    SELECT LOWER(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                TRIM(title),
                '[^a-zA-Z0-9\s-]', '', 'g'
            ),
            '\s+', '-', 'g'
        )
    );
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION set_post_slug()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.slug IS NULL OR NEW.slug = '' THEN
        NEW.slug := generate_slug(NEW.title);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_post_slug
BEFORE INSERT ON posts
FOR EACH ROW EXECUTE FUNCTION set_post_slug();

---

Performance Considerations

Object	Performance Notes
Regular view	Query re-executed each time; no storage overhead
Materialised view	Fast reads; needs explicit refresh; uses storage
SQL function (IMMUTABLE)	Can be inlined by planner; very fast
PL/pgSQL function	Cannot be inlined; small overhead per call
Trigger	Adds overhead to every DML operation; keep logic minimal
Trigger + complex logic	Profile carefully; complex triggers can make writes slow

-- Check function/trigger overhead with EXPLAIN
EXPLAIN ANALYZE UPDATE posts SET title = 'Test' WHERE id = 1;
-- Watch "Trigger..." lines in output

---

Managing Database Code

Keep your database code version-controlled like application code:

-- Use CREATE OR REPLACE for idempotent deployments
CREATE OR REPLACE FUNCTION calculate_reading_time(...) ...
CREATE OR REPLACE VIEW published_posts AS ...

-- For triggers, drop and recreate to handle changes
DROP TRIGGER IF EXISTS trg_posts_updated_at ON posts;
CREATE TRIGGER trg_posts_updated_at ...

-- List all functions
SELECT routine_name, routine_type, data_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- List all views
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'public'
ORDER BY table_name;

-- List all triggers
SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM information_schema.triggers
WHERE trigger_schema = 'public'
ORDER BY event_object_table, trigger_name;

---

What I Learned About Database Logic

Moving query logic into views and functions transformed my blog codebase. The seven copy-pasted queries became one published_posts view. When I needed to add a featured flag filter, I changed it in one place—the view—not seven.

When to put logic in the database:

• ✅ Business rules that must be enforced regardless of which application accesses the DB

• ✅ Complex queries reused across many places

• ✅ Computed columns (reading time, slug, audit timestamps)

• ✅ Referential integrity enforcement

When to keep logic in the application:

• ✅ Business workflows that need external API calls

• ✅ Complex conditional logic that changes frequently

• ✅ Logic that needs to be unit-tested easily

• ✅ User-facing validation with detailed error messages

Golden rules:

• ✅ Views for shared, parameterless queries

• ✅ Functions for parameterised, reusable logic

• ✅ Stored procedures for multi-step transactional workflows

• ✅ Triggers for automatic audit logging and derived column maintenance

• ❌ Avoid heavy business logic in triggers—it's hard to debug

---

Next Steps

With powerful database logic in place, the next priority is protecting your data: Database Security.

• → Next: Database Security

• ← Previous: Transactions and ACID

---

Part of the Database 101 Series