Part VI — Database Programming and Application Development

Chapter 22. Stored Procedures, Functions, and Triggers

SQL is declarative; databases also speak a procedural dialect — programs that live inside the database, run next to the data, and are callable by name from any client: stored procedures and functions (the verbs), and triggers (programs the database runs for you when data changes). This chapter teaches all three on both platforms — PL/pgSQL and MySQL's SQL/PSM-based language — and places them honestly: stored programs are the database's enforcement layer for the rules Chapter 9's constraints cannot state (Chapter 20's ladder), and the platform's most unportable surface, where the Chapter 13 dialect-layer discipline applies with full force.

The throughline is the registrar's toolkit: a grade_points function, an enroll_student procedure with the seat-guard of Chapter 18, capacity and audit triggers, and a cursor-based grade poster — every piece used again by the application chapters that follow.

After studying this chapter you will be able to:

• Decide when logic belongs in the database and when it does not.
• Write procedures and functions with parameters and return values on both platforms.
• Write PL/pgSQL and MySQL stored programs: declarations, control flow, loops.
• Create triggers on insert/update/delete events with OLD/NEW rows.
• Use cursors deliberately, knowing why set-first beats row-by-row.
• Handle and raise errors in the database.
• Enforce business rules with the rule ladder: constraint → trigger → procedure.
• Compare the two platforms' stored-programming approaches and their portability cost.

22.1 Stored procedures and user-defined functions

A stored procedure is a named program executed with CALL; a user-defined function (UDF) is a named computation used inside SQL — SELECT grade_points('A'). The distinction is real: functions return a value and live in expressions; procedures run statements, may return many rows or nothing, and are endpoints (CALL enroll_student(21600001, 12);).

What stored programs buy: closeness to data (one round-trip, no shipping rows to the client), a callable API (the application calls enroll_student and the database enforces the whole choreography — validation, seat check, insert — atomically), reuse (psql, the GUI, and three application languages all call the same function), and authorization seams (GRANT EXECUTE — the portal account gets the procedure, not the tables). What they cost: a second language in the system (testing and versioning now cover SQL and procedural code), platform lock (Section 22.9's near-zero portability), and logic that hides from application-side debugging.

The honest placement rule, from Chapter 20's ladder: constraints first; then triggers for cross-table rules; then procedures for multi-statement choreography; application code for everything else. Stored programs are not where logic goes to be professional — they are where specific logic goes: atomic, shared, data-bound.

22.2 Parameters and return values

Parameters come in three modes: IN (input, default), OUT (output, set by the program), INOUT (both). Functions return a declared type; procedures may have OUT parameters and result sets.

The chapter's reusable function, on both platforms — the grade-to-points map of Chapter 11, promoted from a CASE expression to a named, tested, callable unit:

-- PostgreSQL (PL/pgSQL)
CREATE FUNCTION grade_points(g CHAR(2)) RETURNS NUMERIC(3,1) AS $$
BEGIN
    RETURN CASE g
             WHEN 'A+' THEN 4.0  WHEN 'A'  THEN 4.0
             WHEN 'A-' THEN 3.7  WHEN 'B+' THEN 3.3
             WHEN 'B'  THEN 3.0  WHEN 'B-' THEN 2.7
             WHEN 'C+' THEN 2.3  WHEN 'C'  THEN 2.0
             WHEN 'C-' THEN 1.7  WHEN 'D'  THEN 1.0
             WHEN 'F'  THEN 0.0  ELSE NULL
           END;
END;
$$ LANGUAGE plpgsql;

SELECT full_name, grade, grade_points(grade) AS points
FROM   enrollment e JOIN student s USING (student_id)
WHERE  e.section_id = 8 AND e.grade IS NOT NULL;

   full_name   | grade | points
---------------+-------+--------
 Nusrat Jahan  | B+    |    3.3
 Rakib Hasan   | A     |    4.0
 Sadia Afrin   | A-    |    3.7
 Farhan Akter  | C+    |    2.3

-- MySQL: same logic, different shell
DELIMITER $$
CREATE FUNCTION grade_points(g CHAR(2)) RETURNS DECIMAL(3,1)
DETERMINISTIC
BEGIN
    RETURN CASE g
             WHEN 'A+' THEN 4.0 WHEN 'A' THEN 4.0 WHEN 'A-' THEN 3.7
             WHEN 'B+' THEN 3.3 WHEN 'B' THEN 3.0 WHEN 'B-' THEN 2.7
             WHEN 'C+' THEN 2.3 WHEN 'C' THEN 2.0 WHEN 'C-' THEN 1.7
             WHEN 'D'  THEN 1.0 WHEN 'F' THEN 0.0 ELSE NULL
           END;
END$$
DELIMITER ;

The shell differences to note now, before they repeat: PostgreSQL quotes the body with $$ (dollar-quoting — no escaping of inner quotes) and declares the language; MySQL switches the client DELIMITER so the body's semicolons reach the server whole, and requires DETERMINISTIC (or NOT DETERMINISTIC/READS SQL DATA) declarations when the binary log is on — a replication-trust declaration, not style.

22.3 PostgreSQL PL/pgSQL

PL/pgSQL's essentials, as the seat-guard procedure shows them:

CREATE PROCEDURE enroll_student(p_student INTEGER, p_section INTEGER) AS $$
DECLARE
    v_capacity SMALLINT;
    v_enrolled INTEGER;
BEGIN
    SELECT capacity INTO v_capacity
    FROM   course_section
    WHERE  section_id = p_section
    FOR UPDATE;                              -- Chapter 18's seat lock

    SELECT COUNT(*) INTO v_enrolled
    FROM   enrollment
    WHERE  section_id = p_section;

    IF v_enrolled >= v_capacity THEN
        RAISE EXCEPTION 'section % is full (%/%)',
                        p_section, v_enrolled, v_capacity
            USING HINT = 'Try another section or the waitlist.';
    END IF;

    INSERT INTO enrollment (student_id, section_id, grade)
    VALUES (p_student, p_section, NULL);
END;
$$ LANGUAGE plpgsql;

CALL enroll_student(21600001, 12);          -- Zara joins Database Systems

The anatomy: DECLARE for locals (typed, defaultable); SELECT ... INTO for single-row fetches; IF/ELSIF/ELSE, LOOP, EXIT WHEN, and FOR var IN (query) LOOP for control; RAISE EXCEPTION ... USING HINT for errors that carry remediation; and FOR UPDATE inside the procedure — Chapter 18's lock, now institutionalized where every client inherits it. One PostgreSQL formality: procedures and functions live in the catalog (pg_proc — Chapter 4's metadata promise, again) and are dropped by name with DROP PROCEDURE enroll_student(INTEGER, INTEGER); — signature included, because overloads exist.

22.4 MySQL stored-programming language

The same procedure in MySQL's dialect (SQL/PSM-based) — the shell changes more than the idea:

DELIMITER $$
CREATE PROCEDURE enroll_student(IN p_student INT, IN p_section INT)
BEGIN
    DECLARE v_capacity SMALLINT;
    DECLARE v_enrolled INT;

    SELECT capacity INTO v_capacity FROM course_section
    WHERE  section_id = p_section FOR UPDATE;

    SELECT COUNT(*) INTO v_enrolled FROM enrollment
    WHERE  section_id = p_section;

    IF v_enrolled >= v_capacity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'section is full';
    END IF;

    INSERT INTO enrollment (student_id, section_id, grade)
    VALUES (p_student, p_section, NULL);
END$$
DELIMITER ;

CALL enroll_student(21600001, 12);

MySQL's specifics: parameters carry their mode in the signature (IN p_student INT); DECLARE must lead the body (declarations, then handlers, then statements — the order is enforced and a classic error source); SIGNAL SQLSTATE '45000' is the user-defined error (the application-defined range, Section 22.7); loops are WHILE, REPEAT ... UNTIL, and LOOP with LEAVE/ITERATE; and events (Chapter 16) complete the family — procedures on a calendar, which PostgreSQL schedules outside the server (cron or pg_cron).

22.5 Triggers and trigger events

A trigger is a program attached to a table event — the database running your logic on every change, no client cooperation required (the always-on layer Chapter 20 promised). The grammar both platforms share: event (INSERT/UPDATE/DELETE), timing (BEFORE/AFTER), and granularity (FOR EACH ROW), with NEW and OLD as the after/before row images.

The audit trail — every grade change recorded, who-when-what:

-- PostgreSQL
CREATE TABLE grade_audit (
    student_id INTEGER, section_id INTEGER,
    old_grade CHAR(2), new_grade CHAR(2),
    changed_at TIMESTAMP DEFAULT now()
);

CREATE FUNCTION record_grade_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO grade_audit (student_id, section_id, old_grade, new_grade)
    VALUES (OLD.student_id, OLD.section_id, OLD.grade, NEW.grade);
    RETURN NEW;                              -- proceed with the update
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER grade_audit_trail
AFTER UPDATE OF grade ON enrollment
FOR EACH ROW
WHEN (OLD.grade IS DISTINCT FROM NEW.grade)
EXECUTE FUNCTION record_grade_change();

-- MySQL: same trigger, its dialect
CREATE TRIGGER grade_audit_trail
AFTER UPDATE ON enrollment
FOR EACH ROW
BEGIN
    IF NOT (OLD.grade <=> NEW.grade) THEN     -- NULL-safe comparison
        INSERT INTO grade_audit (student_id, section_id, old_grade, new_grade)
        VALUES (OLD.student_id, OLD.section_id, OLD.grade, NEW.grade);
    END IF;
END$$                                        -- with DELIMITER around creation

Three design points carry the section. RETURN NEW (PostgreSQL) is the trigger's vote: return the row to proceed (a BEFORE trigger may even modify NEW — data-cleaning triggers); OLD.grade IS DISTINCT FROM NEW.grade / MySQL's <=> NULL-safe compare keeps the audit silent when a statement rewrites the same value. And the BEFORE-CHECK pattern puts the capacity guard on every insert path — a second line of defense behind the procedure: BEFORE INSERT ... IF v_enrolled >= v_capacity THEN RAISE/SIGNAL ... — so the direct INSERT that skips the procedure still cannot oversell. Triggers are the rule the application cannot route around; that is their entire value and their entire risk (hidden work on every write — document them, and keep them small).

22.6 Cursors and row-by-row processing

A cursor iterates a result set row by row inside a program — the deliberate machinery of last resort. The canonical use: the end-of-semester grade poster, walking in-progress enrollments:

-- PostgreSQL: but first, the set-based version
UPDATE enrollment SET grade = compute_grade(student_id, section_id)
WHERE  grade IS NULL;                        -- one statement, all rows

Set-based first, always — that single UPDATE is the lesson of this section: SQL's set-at-a-time nature (Chapter 1) makes most cursor loops unnecessary and slower. Cursors earn their complexity only when each row needs sequential logic — external calls, ordered dependencies, partial-progress checkpoints:

-- MySQL: a genuine cursor case — post grades with a progress log
DELIMITER $$
CREATE PROCEDURE post_grades()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_student INT; DECLARE v_section INT;
    DECLARE cur CURSOR FOR
        SELECT student_id, section_id FROM enrollment WHERE grade IS NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_student, v_section;
        IF done THEN LEAVE read_loop; END IF;
        -- per-row sequential logic here (validation, external check, ...)
        UPDATE enrollment SET grade = 'B'      -- placeholder logic
        WHERE  student_id = v_student AND section_id = v_section;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

The pieces: DECLARE ... CURSOR FOR, the NOT FOUND handler ending the loop (MySQL's idiomatic cursor exit), OPEN/FETCH/CLOSE, and a labeled loop with LEAVE. PostgreSQL's equivalents exist (FOR r IN SELECT ... LOOP — the implicit cursor that covers 90% of PostgreSQL's row-walking, explicit REFCURSOR for the rest). The section's rule, one line: a cursor is a confession that the problem is not set-shaped — check twice.

22.7 Exception and error handling

Programs fail; the database's answer is structured. PostgreSQL wraps blocks in EXCEPTION — errors are catchable by condition name, and RAISE rethrows:

BEGIN
    CALL-like logic ...
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'already enrolled: %', SQLERRM;
    WHEN OTHERS THEN
        RAISE;                                -- rethrow what you cannot handle
END;

SQLSTATE and SQLERRM carry the code and message (23505 unique violation, 40P01 deadlock — the Chapter 18 codes resurface), and GET STACKED DIAGNOSTICS extracts detail (constraint name, the failing statement) for real error reporting. MySQL's model is the handler declared up front (DECLARE EXIT HANDLER FOR SQLEXCEPTION — rollback and resignal is the classic pattern) and **SIGNAL/RESIGNAL** for raising: SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' is the user-error convention both applications and this book's procedures use. The professional shape is shared: catch what you can fix, resignal everything else — a stored program that swallows errors silently is a bug factory with a schema attached.

22.8 Enforcing business rules in the database

The chapter's tools, arranged as the ladder (Chapter 20's order) — where each university rule actually lives:

Two rules earn their rows as anti-patterns: enforcing UI validation in triggers (the application's job — the database cannot render messages) and audit via triggers on every table (audit what the compliance questions ask for — Section 20.9 — not everything). The ladder's discipline is judgment: each rung down adds power and cost; the question for every rule is the highest rung that can state it — because the higher the rung, the more clients and attackers it covers with less code.

22.9 Comparing PostgreSQL and MySQL stored-programming approaches

The comparison that closes the chapter — the most honest in the book, because here the platforms genuinely diverge:

The closing judgments. First, portability is lowest here: Chapter 17's 95/5 rule inverts for stored programs — the bodies are 95% different, and a migration rewrites them all; the practical strategy is keeping procedural logic thin and test-covered, with the chapter's rule ladder preferring constraints (fully portable) at every opportunity. Second, each platform's gift: PostgreSQL's language is richer (typed variables, better diagnostics, transition tables) and its ecosystem treats procedures as tested code; MySQL's events and handler model are operationally convenient and battle-proven at enormous scale. Third, and deciding: the question is never "which language is better" but "which rules belong in the database at all" — and that answer, the rule ladder, is identical on both platforms.

Chapter Summary

• Procedures are CALLed programs; functions are SQL-callable computations; triggers are event-attached programs the database runs for you.
• Stored programs buy atomicity-closeness, a callable API, reuse, and GRANT EXECUTE seams; they cost a second language, testing discipline, and platform lock.
• Parameters: IN/OUT/INOUT; functions declare RETURNS; the grade_points function demonstrates both platforms' shells.
• PL/pgSQL: dollar-quoting, DECLARE, SELECT INTO, control flow, RAISE EXCEPTION with hints, FOR UPDATE institutionalized; MySQL: DELIMITER, DECLARE-first, SIGNAL 45000, handlers, and events for scheduling.
• Triggers share grammar (event, timing, FOR EACH ROW, NEW/OLD) and differ in dialect; BEFORE-triggers can guard and modify; RETURN NEW votes to proceed; the capacity guard behind the procedure is defense in depth.
• Cursors are the last resort — set-based SQL first; the NOT FOUND handler and labeled loops are MySQL's idiom, FOR-IN-query PostgreSQL's.
• Errors: catch what you can fix, resignal the rest; SQLSTATE codes (23505, 40P01, 40001) surface through both engines.
• The rule ladder places each business rule at its highest stating rung: constraints, then triggers, then procedures, then application code.
• The comparison inverts 95/5: procedural bodies are nearly unportable; keep the layer thin, prefer constraints, test what you write.

Key Terms

Laboratory Exercises

1. Build and prove grade_points on both platforms: run the Section 22.2 verification query, then edge cases — grade_points('F') (0.0), grade_points(NULL) (NULL), grade_points('Z') (NULL), and use it to compute section 8's average points (3.3 + 4.0 + 3.7 + 2.3 = 13.3, average 3.325). Expected results: the four edge values, and 3.33 rounded — the CASE of Chapter 11, now a reusable unit.
2. Run enroll_student(21600001, 12) in university_dev on both platforms, verify the new enrollment and the seat logic by calling it once more (still admitted — capacity 35), then temporarily set section 12's capacity to its current enrollment and call again to capture the full-section error on both platforms. Expected results: enrollment 28 → 29 after the first call; the second call errors with the custom message; capacity restored after the exercise.
3. Trigger audit: install the grade_audit trigger pair; UPDATE Nusrat's section-8 grade from B+ to A- (in dev), and again to A- (no change); inspect the audit table. Expected results: exactly one audit row (B+ → A-); the no-op update writes nothing — IS DISTINCT FROM doing its job.
4. Defense in depth: add the BEFORE INSERT capacity trigger behind enroll_student; then attempt a direct INSERT that the procedure would have blocked (with capacity artificially at the limit) and show the trigger catches it. Expected result: the bypass attempt fails with the trigger's error — the always-on layer demonstrated.
5. Cursor versus set: implement the grade poster both ways (set-based UPDATE and the cursor loop) over the 8 in-progress enrollments in dev; time both and write the one-paragraph verdict. Expected results: identical 8 rows updated; the set-based form measurably faster — and simpler to prove correct.
6. Error paths: write a procedure that deliberately violates a constraint inside an EXCEPTION block / handler, logs a notice, resignals; capture the surfaced SQLSTATE from the client. Expected results: the notice logged, the error re-raised with code 23505 — catch-what-you-fix, resignal-the-rest, demonstrated.

Review Questions and Exercises

1. Give the two structural differences between a procedure and a function. Procedures are CALLed and run statements (OUT params, result sets); functions return a value and appear in expressions.
2. Name three things stored programs buy and two they cost. Buy: data-closeness/atomicity, a callable API, GRANT EXECUTE seams; cost: a second language to test/version, near-zero portability.
3. Why does MySQL require DELIMITER switching and PostgreSQL not? *MySQL's client ends statements at ; — the body's semicolons would terminate creation early; the delimiter is moved out of the way; PostgreSQL's dollar-quoting defines the body's boundaries server-side.*
4. What does DETERMINISTIC declare in MySQL, and when is it required? That a function's output depends only on its inputs — a binary-log/replication trust declaration; required for function creation with the binlog on unless the server trusts creators.
5. Explain RETURN NEW in a BEFORE trigger and one real use. It votes to proceed and may carry modifications — a BEFORE trigger rewriting NEW.full_name := TRIM(NEW.full_name) is a data-cleaning trigger.
6. Why is OLD.grade IS DISTINCT FROM NEW.grade (or <=>) the right audit condition? Plain <> is NULL-unknown for NULLs — setting or clearing a grade would audit nothing; the NULL-safe form treats NULL as a value.
7. What does the WHEN clause add to a PostgreSQL trigger, and what is MySQL's equivalent discipline? A declarative condition so the body only runs for matching rows; MySQL puts the same test inside the body's IF — behavior equal, placement different.
8. State the set-first rule and the two cases that justify a cursor. Do it in one statement unless the problem is not set-shaped; cursors earn their cost for sequential per-row logic (external calls, ordered dependencies, progress checkpoints).
9. Which SQLSTATEs surface in this chapter's procedures, and what does each mean? 23505 unique violation; 40P01 deadlock; 40001 serialization failure; 45000 the user-defined error range.
10. Where does the "enrolled ≤ capacity" rule live in this chapter, and why in two places? Procedure (FOR UPDATE check — the API path) and BEFORE INSERT trigger (the always-on layer) — defense in depth: direct writes cannot bypass the rule.
11. Why are UI-validation rules in triggers an anti-pattern, and what belongs there instead? The database cannot render messages; triggers hold data rules (audit, invariants), the application holds interaction rules.
12. Your team plans a platform migration. What is the stored-program strategy and why? Thin, test-covered procedural layer; prefer constraints (portable) at every ladder rung; treat bodies as a rewrite module with dual-run verification — Chapter 17's method applied where it costs most.

Mini-Project

Build the registrar's database toolkit, registrar_toolkit.sql (both platforms, two files): (1) grade_points and a compute_gpa(student_id) function over graded enrollments (set-based, verified against the canonical GPAs in dev where data matches); (2) enroll_student with the full choreography — validation (student and section exist), the FOR UPDATE seat check, the insert, and a withdraw_student sibling handling the "grade already posted" refusal; (3) the grade_audit trigger pair plus a capacity guard BEFORE INSERT trigger; (4) post_grades(section_id) both ways — set-based and cursor — with a comment explaining which you ship and why; (5) an exception-hardened transfer_section(student, from, to) that demonstrates catch-and-resignal with the deadlock/serialization codes documented; (6) the GRANT set: the portal account gets EXECUTE and SELECT on a transcript view — and no direct table grants. Every program: a header comment stating its contract (parameters, effects, errors), a test call with expected output, and its rung on the rule ladder. This toolkit is the database half of Chapter 23–24's application — the application will do nothing but call it.