Functions and procedures in PL/pgSQL

Part of the free LogicWiz SQL & Databases course, Module 15: Views, functions and security. The queries and practice run in the app on real Postgres.

Three apps move money between UPI accounts, and each one checks the balance in its own slightly different way. One forgets. Aarav wants one transfer that lives inside the database, so every app calls the same rules.

You'll learn to

• Write SQL and PL/pgSQL functions and call them in a SELECT
• Use variables, IF and loops, and return a value or a whole table
• Write a procedure, CALL it, and stop bad input with RAISE EXCEPTION

Learn

Code that lives in the database

A function is a named piece of logic stored in the database. You call it inside a query, like UPPER(name), except you wrote it.

• It takes parameters and returns a value (or a set of rows).
• Every app that calls it gets the same rule: the GST formula, the balance check, the discount slab.
• The simplest kind is written in plain SQL: LANGUAGE sql.

The body sits between $ ... $. That is just a quote mark that lets you write ' inside without escaping.

A one-line SQL function

CREATE FUNCTION gst(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
AS $
  SELECT ROUND(amount * 0.18, 2)
$;

SELECT txn_id, amount, gst(amount) AS gst
FROM upi_txns
WHERE txn_id <= 5;

Once created, gst() works in any query, on any column or value: SELECT gst(999); too.

PL/pgSQL: variables, IF and loops

When one SELECT is not enough, use PL/pgSQL, Postgres's own procedural language.

• DECLARE lists variables: b NUMERIC;
• BEGIN ... END; holds the steps.
• := assigns: total := total + i;
• SELECT col INTO var FROM ... puts a query's value into a variable.
• IF ... THEN ... ELSIF ... ELSE ... END IF; decides; FOR i IN 1..n LOOP ... END LOOP; repeats.
• RETURN value; sends the answer back.
• RAISE NOTICE 'x is %', x; prints a message; RAISE EXCEPTION stops with an error.

A PL/pgSQL function with IF

CREATE FUNCTION balance_band(p_acc INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $
DECLARE
  b NUMERIC;
BEGIN
  SELECT balance INTO b FROM accounts WHERE account_id = p_acc;
  IF b IS NULL THEN
    RETURN 'no such account';
  ELSIF b < 1000 THEN
    RETURN 'low';
  ELSIF b < 50000 THEN
    RETURN 'medium';
  ELSE
    RETURN 'high';
  END IF;
END;
$;

SELECT account_id, holder, balance, balance_band(account_id) AS band
FROM accounts
ORDER BY account_id;

The parameter is called p_acc, not account_id. Naming it like a column causes the bug in the warning below.

Returning a whole table

CREATE FUNCTION paid_by(p_acc INT)
RETURNS TABLE (txn_id INT, paid_to VARCHAR, amount NUMERIC)
LANGUAGE plpgsql
AS $
BEGIN
  RETURN QUERY
  SELECT t.txn_id, a.holder, t.amount
  FROM upi_txns t
  JOIN accounts a ON a.account_id = t.to_acc
  WHERE t.from_acc = p_acc AND t.status = 'success'
  ORDER BY t.txn_id;
END;
$;

SELECT * FROM paid_by(1);

RETURNS TABLE (...) plus RETURN QUERY makes a function you can put in FROM, like a table that takes parameters.

A procedure: CALL it to do work

CREATE PROCEDURE transfer(p_from INT, p_to INT, p_amt NUMERIC)
LANGUAGE plpgsql
AS $
BEGIN
  IF (SELECT balance FROM accounts WHERE account_id = p_from) < p_amt THEN
    RAISE EXCEPTION 'Account % has too little money', p_from;
  END IF;
  UPDATE accounts SET balance = balance - p_amt WHERE account_id = p_from;
  UPDATE accounts SET balance = balance + p_amt WHERE account_id = p_to;
  RAISE NOTICE 'Moved % from % to %', p_amt, p_from, p_to;
END;
$;

CALL transfer(2, 5, 1000);

SELECT account_id, holder, balance
FROM accounts
WHERE account_id IN (2, 5);

Try CALL transfer(5, 1, 99999);: the exception stops it and neither UPDATE happens.

Function vs procedure

Never give a parameter the same name as a column. In WHERE account_id = account_id, PL/pgSQL cannot tell the parameter from the column and stops with column reference "account_id" is ambiguous (a plain SQL function silently picks the column, and the WHERE becomes always true). Prefix parameters: p_acc, p_city.

MySQL writes stored routines differently: DELIMITER // around the body, DECLARE inside BEGIN, SET x = ... to assign, and SIGNAL SQLSTATE '45000' instead of RAISE EXCEPTION. The ideas (parameters, IF, loops, CALL for procedures) are the same.

In one line

Functions return values and are used in queries (SELECT f(x)); procedures do work and are run with CALL p(x).

Interview corner

What is the difference between a function and a stored procedure?

A function returns a value or a set of rows and is called inside SQL (SELECT gst(amount)); it cannot commit. A procedure is called with CALL, returns nothing (or INOUT values) and can COMMIT or ROLLBACK inside. Use functions for calculations and lookups, procedures for multi-step jobs.

Why put business logic in the database, and why not?

For: one rule shared by every app, fewer round trips (one CALL instead of five queries), and data checks that no client can skip. Against: logic is harder to version, test and debug than app code, it ties you to one database's dialect, and heavy logic on the database server is hard to scale out. Most teams keep integrity rules in the database and business workflows in the app.

How do you raise an error from a PL/pgSQL function?

RAISE EXCEPTION 'Account % is frozen', p_acc; stops the function and aborts the statement (and the transaction, unless it is caught with an EXCEPTION block). RAISE NOTICE only prints a message and carries on.