# 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](#introduction-the-copy-paste-query-problem)
2. [Views: Named, Reusable Queries](#views-named-reusable-queries)
3. [Materialised Views: Cached Query Results](#materialised-views-cached-query-results)
4. [PostgreSQL Functions (User-Defined Functions)](#postgresql-functions-user-defined-functions)
5. [Stored Procedures](#stored-procedures)
6. [Triggers: Automatic Database Reactions](#triggers-automatic-database-reactions)
7. [PL/pgSQL: Writing Procedural Code in PostgreSQL](#plpgsql-writing-procedural-code-in-postgresql)
8. [Common Patterns and Use Cases](#common-patterns-and-use-cases)
9. [Performance Considerations](#performance-considerations)
10. [Managing Database Code](#managing-database-code)
11. [What I Learned About Database Logic](#what-i-learned-about-database-logic)
12. [Next Steps](#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

```sql
-- 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:

```sql
-- 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

```sql
-- 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:

```sql
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', 'new@example.com');
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

```sql
-- 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:

```sql
-- 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:

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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)

```sql
-- 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

{% @mermaid/diagram content="graph TD
Event\["DML Event\n(INSERT/UPDATE/DELETE)"] --> Timing
Timing{Timing} --> BEFORE
Timing --> AFTER
Timing --> INSTEAD\_OF
BEFORE --> RowLevel\["ROW level\n(once per affected row)"]
BEFORE --> StmtLevel\["STATEMENT level\n(once per SQL statement)"]" %}

### Auto-Update `updated_at` Timestamp

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
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

```sql
-- 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

```sql
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

```sql
-- 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

```sql
-- 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 |

```sql
-- 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:

```sql
-- 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**](https://blog.htunnthuthu.com/getting-started/programming/database-101/database-101-database-security)
* [**← Previous: Transactions and ACID**](https://blog.htunnthuthu.com/getting-started/programming/database-101/database-101-transactions-and-acid)

***

*Part of the* [*Database 101 Series*](https://blog.htunnthuthu.com/getting-started/programming/database-101)