Advanced SQL

Why Go Beyond Plain SQL?

SQL is declarative — great for describing what you want, clumsy for how to compute it step by step. Sometimes you need loops, conditionals, and reusable logic. SQL:1999 added functions, procedures, and control-flow constructs to bridge that gap, turning SQL into something closer to a general-purpose language.

Functionsreturn a value or a table

Proceduresreturn results through out parameters

Constructsloops, if, case, exceptions

Triggersrun automatically on a change

NoteNote

The lecture syntax below is the SQL standard. PostgreSQL (used by the course datasets) writes the same ideas in its procedural language PL/pgSQL, so each standard example is followed by a version you can run.

---

SQL Functions

A function returns a single value, and can be written in SQL itself — or in an external language (useful for specialized types like images or geometry, e.g. “do these two polygons overlap?”).

create function dept_count(dept_name varchar(20))
returns integer
begin
    declare d_count integer;
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_name;
    return d_count;
end

The same function in PostgreSQL, on the university dataset:

create or replace function dept_count(d_name varchar(20))
returns integer as $$
declare
    d_count integer;
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = d_name;
    return d_count;
end;
$$ language plpgsql;

Use it just like any expression:

select dept_name, budget
from department
where dept_count(dept_name) > 4;

Keyword	Meaning
begin ... end	A compound statement — holds multiple SQL statements together
returns	Declares the type of the result (e.g., integer)
return	Specifies the actual value sent back when the function finishes

NoteKey idea

Think of SQL functions as parameterized views — a view that generalizes by accepting arguments.

Table Functions

Added in SQL:2003 — instead of returning one value, a table function returns an entire relation.

create function instructor_of(dept_name varchar(20))
returns table (
    id varchar(5),
    name varchar(20),
    dept_name varchar(20),
    salary numeric(8,2)
)
return table
    (select id, name, dept_name, salary
     from instructor
     where instructor.dept_name = instructor_of.dept_name);

select * from table(instructor_of('Biology'));

In PostgreSQL, the function is called directly in from:

create or replace function instructor_of(d_name varchar(20))
returns table (id varchar(5), name varchar(20),
               dept_name varchar(20), salary numeric(8,2)) as $$
    select i.id, i.name, i.dept_name, i.salary
    from instructor as i
    where i.dept_name = d_name;
$$ language sql;

select * from instructor_of('Biology');

Procedures

Where a function returns a value via return, a procedure passes results back through out parameters.

create procedure dept_count_proc(
    in dept_name varchar(20),
    out d_count integer)
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = dept_count_proc.dept_name;
end

declare d_count integer;
call dept_count_proc('Physics', d_count);

create or replace procedure dept_count_proc(
    in d_name varchar(20),
    out d_count integer) as $$
begin
    select count(*) into d_count
    from instructor
    where instructor.dept_name = d_name;
end;
$$ language plpgsql;

call dept_count_proc('Physics', null);

NoteNote

SQL:1999 allows overloading — multiple functions/procedures can share a name as long as their parameter count or types differ.

---

Procedural Language Constructs

NoteNote

Most databases implement their own dialect of these — syntax varies across systems.

Loops

-- while: check condition before each iteration
while boolean_expression do
    sequence_of_statements;
end while;

-- repeat: check condition after each iteration (always runs once)
repeat
    sequence_of_statements;
until boolean_expression
end repeat;

-- for: iterate directly over query results
declare total integer default 0;
for r as
    select budget from department
do
    set total = total + r.budget;
end for;

The for loop above, run in PostgreSQL as a function that returns the total:

create or replace function total_budget() returns numeric as $$
declare
    total numeric := 0;
    r record;
begin
    for r in select budget from department loop
        total := total + r.budget;
    end loop;
    return total;
end;
$$ language plpgsql;

select total_budget();

fetch next row

into r

→

add

total := total + r.budget

→

repeat

until no rows remain

Conditionals

-- if / elseif / else
if boolean_expression then
    sequence_of_statements;
elseif boolean_expression then
    sequence_of_statements;
else
    sequence_of_statements;
end if;

-- simple case: match a variable against fixed values
case dept_name
    when 'Biology' then ...
    when 'Physics' then ...
    else ...
end case;

-- searched case: match arbitrary expressions
case
    when salary > 100000 then ...
    when salary > 50000 then ...
    else ...
end case;

A searched case on the university dataset (inside a query, case ends with end):

select name, salary,
       case
           when salary > 100000 then 'high'
           when salary > 50000 then 'medium'
           else 'low'
       end as band
from instructor;

Exception Handling

declare out_of_classroom_seats condition;
declare exit handler for out_of_classroom_seats
begin
    ...
    signal out_of_classroom_seats;
    ...
end

The exit handler terminates the enclosing begin...end block when the condition fires. Other handler behaviors are also possible (e.g., continue instead of exit).

In PostgreSQL, an exception section handles the error (and raise exception signals your own):

create or replace function add_instructor(i_id varchar(5), i_name varchar(20),
                                          d_name varchar(20), sal numeric)
returns text as $$
begin
    insert into instructor values (i_id, i_name, d_name, sal);
    return 'added ' || i_name;
exception
    when unique_violation then
        return 'id ' || i_id || ' already exists';
end;
$$ language plpgsql;

select add_instructor('63395', 'Copy', 'Biology', 50000);   -- id already exists

---

Triggers

A trigger is code that fires automatically in response to an insert, update, or delete on a table — no one has to remember to call it.

Defining a trigger means specifying three things:

1. Event — insert / update / delete
2. Timing — BEFORE or AFTER the event
3. Action — what to actually do

Event

insert / update / delete

→

BEFORE trigger

validate or modify values first

→

Database operation

executes

→

AFTER trigger

update other tables, audit, alert

BEFORE vs. AFTER

Timing	Typical use
BEFORE	Clean up or validate incoming values before they’re written (e.g., normalize blanks to null); raise errors before a delete proceeds
AFTER	Update other tables to stay in sync, maintain audit trails, enforce cross-table integrity, trigger alerts/notifications

Row-Level vs. Statement-Level

Type	Behavior
Row-level	Fires once per affected row. A 10% raise across 100 instructors fires the trigger 100 times.
Statement-level	Fires once per statement, regardless of how many rows were touched — uses referencing old table / referencing new table to access all affected rows at once via transition tables. More efficient for bulk updates.

Example: BEFORE trigger (data cleanup)

Convert blank grades to null before they’re written:

create trigger setnull_trigger before update of grade on takes
referencing new row as nrow
for each row
when (nrow.grade = ' ')
begin atomic
    set nrow.grade = null;
end;

In PostgreSQL a trigger calls a trigger function:

create or replace function setnull_grade() returns trigger as $$
begin
    if new.grade = ' ' then
        new.grade := null;
    end if;
    return new;
end;
$$ language plpgsql;

drop trigger if exists setnull_trigger on takes;
create trigger setnull_trigger
before update of grade on takes
for each row
execute function setnull_grade();

begin;
update takes set grade = ' ' where id = '1000' and course_id = '642';
select id, course_id, grade from takes where id = '1000' and course_id = '642';
rollback;

Example: AFTER trigger (cross-table maintenance)

Update a student’s earned credits whenever a failing/blank grade turns into a pass:

create trigger credits_earned after update of grade on takes
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null
     and (orow.grade = 'F' or orow.grade is null)
begin atomic
    update student
    set tot_cred = tot_cred +
        (select credits from course where course.course_id = nrow.course_id)
    where student.id = nrow.id;
end;

create or replace function credits_earned() returns trigger as $$
begin
    update student
    set tot_cred = tot_cred +
        (select credits from course where course.course_id = new.course_id)
    where student.id = new.id;
    return new;
end;
$$ language plpgsql;

drop trigger if exists credits_earned on takes;
create trigger credits_earned
after update of grade on takes
for each row
when (new.grade <> 'F' and new.grade is not null
      and (old.grade = 'F' or old.grade is null))
execute function credits_earned();

begin;
select tot_cred from student where id = '1000';
update takes set grade = 'F' where id = '1000' and course_id = '642';
update takes set grade = 'B' where id = '1000' and course_id = '642';   -- F becomes a pass
select tot_cred from student where id = '1000';                         -- 3 credits added
rollback;

Update	old → new grade	Trigger fires?
set grade = 'F'	C- → F	no: new grade is F
set grade = 'B'	F → B	yes: tot_cred + 3

• referencing old row as — access pre-update values (deletes & updates)
• referencing new row as — access post-update values (inserts & updates)
• Triggers can be scoped to specific columns: after update of grade on takes

---

Using Triggers Wisely

Good Uses

• Logging changes to a history/audit table
• Recording metadata an application can’t see (who ran it, when, from where)
• Simple, self-contained validation
• Keeping denormalized or derived values in sync

When to Avoid Triggers

Risk	Why it hurts
Too many triggers	Hard to trace why something changed
Complex trigger logic	Business logic buried where developers won’t look
Cross-server triggers	Network calls hidden inside what looks like a simple update
Triggers calling triggers	Cascading side effects, hard to predict
Recursive triggers enabled	Can spiral — off by default for a reason
Views/procedures/functions nested inside triggers	Performance black box
Iteration inside triggers	Row-by-row processing at scale = slow

Rule of thumb: triggers are best for short, simple, self-contained operations — not as a substitute for your application’s business logic.