Functions, procedures and triggers

SQL is not only queries — you can write programs inside the database too. Functions, procedures and triggers move logic into the database itself. This is where PostgreSQL and MySQL differ the most — switch the course to see both.

Functions

A function returns a value and is used inside a query.

CREATE FUNCTION mark(score int) RETURNS int
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN score >= 86 THEN 5 WHEN score >= 71 THEN 4 WHEN score >= 56 THEN 3 ELSE 2 END
$$;

SELECT student_id, score, mark(score) FROM grades;

$$ … $$ is the function body (quotes inside need no doubling). For more complex logic use LANGUAGE plpgsql: variables, IF, loops.

Procedures

A procedure does not have to return anything; it carries out several actions and is called with CALL.

CREATE PROCEDURE raise_salary(dept int, pct numeric)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE employees SET salary = salary * (1 + pct / 100) WHERE department_id = dept;
END $$;

CALL raise_salary(1, 10);

Triggers

A trigger fires by itself when something happens to a table (INSERT, UPDATE, DELETE). Example: when a medicine is sold, reduce its stock.

CREATE FUNCTION decrease_stock() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE dori SET qoldiq = qoldiq - NEW.miqdor WHERE dori_id = NEW.dori_id;
  RETURN NEW;
END $$;

CREATE TRIGGER sotuv_after_insert
AFTER INSERT ON sotuv
FOR EACH ROW EXECUTE FUNCTION decrease_stock();

In PostgreSQL a trigger has two parts: first a function, then the trigger that calls it.

NEW is the row being inserted (or the updated row), and OLD is the row being deleted (or the previous version).

When to use them

Useful for: keeping data consistent (stock never goes negative), recording a history of changes, doing heavy calculations right next to the data.

Be careful: the logic becomes “invisible” — a programmer writes an INSERT without knowing what else the trigger does. Document triggers and use them sparingly.

Exercise

In free play, load the ready-made pharmacy database, add a column qoldiq int DEFAULT 100 to dori (ALTER TABLE), create the trigger above and add a sale — the stock should go down.

Remember

• A function returns a value, a procedure performs actions (CALL), a trigger reacts to an event.
• PostgreSQL: $$ … $$ and plpgsql; MySQL: DELIMITER and BEGIN … END.
• Triggers are powerful but invisible — use them with care.