Functions and Triggers
PL/pgSQL stored functions, trigger functions, and row-level automation
Skills taught: pg.plpgsql, pg.stored-functions, pg.triggers, pg.new-old
PL/pgSQL stored functions
-- A simple function returning a value
CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC, rate NUMERIC)
RETURNS NUMERIC AS $
BEGIN
RETURN ROUND(price * rate / 100, 2);
END;
$ LANGUAGE plpgsql;
-- Call it
SELECT calculate_tax(99.99, 10); -- 10.00
-- Function with conditional logic
CREATE OR REPLACE FUNCTION get_user_tier(total_spent NUMERIC)
RETURNS TEXT AS $
BEGIN
IF total_spent >= 1000 THEN
RETURN 'platinum';
ELSIF total_spent >= 500 THEN
RETURN 'gold';
ELSIF total_spent >= 100 THEN
RETURN 'silver';
ELSE
RETURN 'bronze';
END IF;
END;
$ LANGUAGE plpgsql;
-- SQL functions (simpler, for single expressions)
CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT AS $
SELECT first || ' ' || last;
$ LANGUAGE sql IMMUTABLE;
Triggers — automatic row actions
-- Most common trigger: auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $
BEGIN
NEW.updated_at = NOW();
RETURN NEW; -- RETURN NEW applies the modified row
END;
$ LANGUAGE plpgsql;
-- Attach to any table that has updated_at
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER trg_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- Now: UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- automatically sets updated_at = NOW() without the app needing to
-- Trigger to log changes
CREATE TABLE audit_log (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name TEXT,
operation TEXT,
old_data JSONB,
new_data JSONB,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $
BEGIN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
);
RETURN NULL; -- AFTER trigger, return value ignored
END;
$ LANGUAGE plpgsql;
Tip: The NEW record holds the new row values (for INSERT/UPDATE). OLD holds the previous values (for UPDATE/DELETE). TG_OP is 'INSERT', 'UPDATE', or 'DELETE'. Return NEW from BEFORE triggers to apply changes; return NULL from AFTER triggers (value ignored).
Frequently Asked Questions
Should I use triggers or application code?
Triggers are good for: (1) updating audit logs, (2) maintaining denormalized data (comment_count), (3) setting updated_at — logic that must happen regardless of how data is modified. Application code is better for complex business logic, sending emails, calling external APIs. Triggers are invisible to the application — both a feature (always runs) and a risk (hard to debug when you don't know they exist).