Skip to content

exercises.md

Latest commit

 

History

History
331 lines (236 loc) · 9.44 KB

exercises.md

File metadata and controls

331 lines (236 loc) · 9.44 KB

Section 17 — Functions, Stored Procedures & Triggers

What you'll practice: writing PL/pgSQL functions and procedures, using CREATE FUNCTION / CREATE PROCEDURE / CALL, and wiring up triggers (including an audit trigger).

PL/pgSQL is the scripting language baked into PostgreSQL. Once you know the basics, you can push logic into the database where it's fast, transactional, and reusable across every app that talks to the DB.

Learning objectives

  • Write a function with CREATE FUNCTION ... LANGUAGE plpgsql
  • Declare parameters and a RETURNS type
  • Use DECLARE / BEGIN / END and local variables
  • Use IF / ELSIF / ELSE, LOOP, FOR ... IN SELECT, RAISE NOTICE, RAISE EXCEPTION
  • Write a stored procedure and call it with CALL
  • Understand the difference between a function and a procedure (procedures can COMMIT)
  • Attach a BEFORE/AFTER trigger on INSERT / UPDATE / DELETE
  • Use the trigger's NEW and OLD pseudo-rows
  • Build an audit trail that records every change

How to run these exercises

psql -U postgres -d sql_exercise -f 04-advanced-databases/17-functions-stored-procedures-and-triggers/schema.sql
psql -U postgres -d sql_exercise -f 04-advanced-databases/17-functions-stored-procedures-and-triggers/seed.sql
psql -U postgres -d sql_exercise

Inside psql:

SET search_path TO adv_funcs;

Exercises

Exercise 1 — Your first function

Write a function add_ints(a INTEGER, b INTEGER) RETURNS INTEGER that returns a + b. Start with a trivial SQL function:

CREATE OR REPLACE FUNCTION add_ints(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE sql
AS $$ SELECT a + b $$;

SELECT add_ints(2, 3);

Question: what's the $$ ... $$ syntax called, and why would you use it instead of regular quotes?

Exercise 2 — PL/pgSQL version of add_ints

Rewrite add_ints as a PL/pgSQL function with a DECLARE block and a local variable.

CREATE OR REPLACE FUNCTION add_ints(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    result INTEGER;
BEGIN
    result := a + b;
    RETURN result;
END
$$;

Verify: SELECT add_ints(10, 20);

Exercise 3 — IF/ELSIF

Write a function describe_balance(balance INTEGER) RETURNS TEXT that returns:

  • 'broke' if balance < 1000
  • 'ok' if balance < 100000
  • 'wealthy' otherwise

Use IF/ELSIF/ELSE.

Verify: SELECT holder, describe_balance(balance) FROM account;

Exercise 4 — Function that queries a table

Write account_count() RETURNS INTEGER that returns SELECT COUNT(*) FROM account.

Verify: SELECT account_count();

Exercise 5 — Function with a query and looping

Write a function total_balance() RETURNS INTEGER that iterates through every account with a FOR rec IN SELECT balance FROM account LOOP and accumulates the total.

CREATE OR REPLACE FUNCTION total_balance()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    rec   RECORD;
    total INTEGER := 0;
BEGIN
    FOR rec IN SELECT balance FROM account LOOP
        total := total + rec.balance;
    END LOOP;
    RETURN total;
END
$$;

(In real life you'd just write SELECT SUM(balance) ... — this is pedagogical.)

Exercise 6 — RAISE NOTICE / RAISE EXCEPTION

Write a function transfer(sender_id INT, receiver_id INT, amount_cents INT) RETURNS VOID that:

  1. Raises an exception if amount_cents <= 0 (RAISE EXCEPTION 'amount must be positive, got %', amount_cents;).
  2. Raises an exception if the sender doesn't have enough.
  3. Otherwise: decrements sender, increments receiver, RAISE NOTICE 'Transferred % cents from % to %', amount_cents, sender_id, receiver_id;

Verify:

SELECT transfer(1, 2, 10000);
SELECT holder, balance FROM account WHERE id IN (1, 2);

Then try a bad transfer to watch the exception fire.

Exercise 7 — Function returning a row

Write get_account(p_id INT) RETURNS account that returns the entire row from account with that id.

CREATE OR REPLACE FUNCTION get_account(p_id INT)
RETURNS account
LANGUAGE plpgsql
AS $$
DECLARE
    rec account;
BEGIN
    SELECT * INTO rec FROM account WHERE id = p_id;
    RETURN rec;
END
$$;

SELECT * FROM get_account(1);

Exercise 8 — SETOF function

Write wealthy_accounts() RETURNS SETOF account that returns every row from account where balance >= 100000. Call it with SELECT * FROM wealthy_accounts();.

Exercise 9 — A stored procedure (not a function)

Write a procedure sp_transfer(sender_id INT, receiver_id INT, amount_cents INT) that does the same thing as Exercise 6 but as a procedure. Call it with CALL sp_transfer(1, 2, 500);.

Question: what's the difference between a function (CREATE FUNCTION) and a procedure (CREATE PROCEDURE)? When would you reach for one over the other?

Exercise 10 — Your first trigger: updated_at

Write a trigger function set_updated_at() that sets NEW.updated_at = NOW(), and attach it as a BEFORE UPDATE trigger on product.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END
$$;

CREATE TRIGGER product_set_updated_at
BEFORE UPDATE ON product
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Verify:

UPDATE product SET price_cents = price_cents + 100 WHERE id = 1;
SELECT id, price_cents, updated_at FROM product WHERE id = 1;

The updated_at should be the current timestamp.

Exercise 11 — Audit trigger on product

Write a trigger function product_audit_trigger() that writes a row into product_audit every time a row in product is INSERTed, UPDATEd, or DELETEd. Attach it as an AFTER trigger for each of those three operations.

CREATE OR REPLACE FUNCTION product_audit_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO product_audit (product_id, action, old_price_cents, new_price_cents)
        VALUES (NEW.id, 'I', NULL, NEW.price_cents);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO product_audit (product_id, action, old_price_cents, new_price_cents)
        VALUES (NEW.id, 'U', OLD.price_cents, NEW.price_cents);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO product_audit (product_id, action, old_price_cents, new_price_cents)
        VALUES (OLD.id, 'D', OLD.price_cents, NULL);
        RETURN OLD;
    END IF;
    RETURN NULL;
END
$$;

CREATE TRIGGER product_audit_ins AFTER INSERT ON product FOR EACH ROW EXECUTE FUNCTION product_audit_trigger();
CREATE TRIGGER product_audit_upd AFTER UPDATE ON product FOR EACH ROW EXECUTE FUNCTION product_audit_trigger();
CREATE TRIGGER product_audit_del AFTER DELETE ON product FOR EACH ROW EXECUTE FUNCTION product_audit_trigger();

Verify:

UPDATE product SET price_cents = 13999 WHERE id = 1;
DELETE FROM product WHERE id = 4;
INSERT INTO product (name, price_cents) VALUES ('New Cam', 9999);

SELECT * FROM product_audit ORDER BY id;

You should see one row for each change.

Exercise 12 — WHEN clause on a trigger

You only care about audit rows when the price actually changes on an UPDATE. Drop the product_audit_upd trigger and recreate it with a WHEN clause:

DROP TRIGGER product_audit_upd ON product;
CREATE TRIGGER product_audit_upd
    AFTER UPDATE ON product
    FOR EACH ROW
    WHEN (OLD.price_cents IS DISTINCT FROM NEW.price_cents)
    EXECUTE FUNCTION product_audit_trigger();

Verify: update name only (not price) — no audit row. Update price — one audit row.

Exercise 13 — Trigger to prevent deletes

Write a trigger that prevents deleting any product whose price_cents > 40000. Raise an exception if someone tries.

CREATE OR REPLACE FUNCTION block_expensive_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.price_cents > 40000 THEN
        RAISE EXCEPTION 'Cannot delete expensive product %', OLD.name;
    END IF;
    RETURN OLD;
END
$$;

CREATE TRIGGER product_block_expensive_delete
BEFORE DELETE ON product
FOR EACH ROW EXECUTE FUNCTION block_expensive_delete();

Verify: try DELETE FROM product WHERE id = 2; (the 4K Monitor at £459) — should error.

Exercise 14 — List all triggers and functions

Query information_schema.triggers and information_schema.routines to list everything you've created in this section.

SELECT trigger_name, event_manipulation, event_object_table
FROM   information_schema.triggers
WHERE  trigger_schema = 'adv_funcs';

SELECT routine_name, routine_type, data_type
FROM   information_schema.routines
WHERE  routine_schema = 'adv_funcs';

Bonus challenges

Bonus 1 — DEFAULT parameters

Rewrite transfer to have a fourth parameter memo TEXT DEFAULT 'transfer' and include it in the RAISE NOTICE. Try calling it with and without that argument.

Bonus 2 — Procedure that commits mid-run

Procedures can COMMIT inside them. Write a procedure that loops over a batch of account ids and commits after every 100 updates. This is how you do "chunked" bulk updates without one giant transaction.

Bonus 3 — Trigger to automatically adjust the ledger

In Section 16 you moved money with explicit transactions that also inserted into a ledger table. Instead, write an AFTER UPDATE trigger on account that writes a ledger row whenever balance changes. (Not always a good idea in practice — why?)

Cleanup

DROP SCHEMA IF EXISTS adv_funcs CASCADE;