Writing Functions in PostgreSQL with PL/pgSQL

PostgreSQL lets you write server-side functions that execute inside the database itself, close to the data. The default and most widely used procedural language for this is PL/pgSQL, which extends plain SQL with variables, conditionals, loops, and exception handling — similar in spirit to a small imperative programming language embedded in SQL.

Functions are useful for encapsulating business logic that should always run consistently regardless of which application or client touches the database, for building reusable calculations, and for powering triggers.

Why it matters

A function stores logic in the database so that every application and report uses the same calculation. PL/pgSQL adds variables, conditions and loops to SQL. You will meet such functions in mature systems, and they are what triggers are built from.

Anatomy of a PL/pgSQL Function

A function is created with CREATE FUNCTION name(parameters) RETURNS return_type AS $$ ... $$ LANGUAGE plpgsql;. The $$ pair is "dollar quoting," which lets you write a multi-line function body without escaping internal quotes. Inside, a DECLARE section (optional) defines local variables, and a BEGIN ... END; block holds the executable logic, ending with a RETURN statement for the declared return type.

Parameters, Logic, and Control Flow

Function parameters are referenced directly by name inside the body. PL/pgSQL supports IF/ELSIF/ELSE, LOOP/WHILE/FOR, and can run ordinary SQL statements (SELECT INTO a variable, INSERT, UPDATE) as part of its logic — meaning a function can both compute a value and have side effects on the database.

Calling a Function

Once created, a function is called like any built-in function: SELECT function_name(arg1, arg2);. Functions can also be used inside larger queries, as default values for columns, or attached to triggers so they run automatically on INSERT/UPDATE/DELETE.

Example

CREATE FUNCTION apply_discount(price NUMERIC, discount NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    capped NUMERIC;
BEGIN
    IF discount > 0.5 THEN
        capped := 0.5;
    ELSE
        capped := discount;
    END IF;

    RETURN price - (price * capped);
END;
$$ LANGUAGE plpgsql;

SELECT apply_discount(100.00, 0.75);

 apply_discount
-----------------
           50.00

Exercise

This exercise uses PL/pgSQL, so it needs a PostgreSQL server. Write a function apply_discount that takes a price and a percentage and returns the discounted price rounded to two decimals. The discount must be capped at 50 per cent, whatever percentage is passed. Then call it twice.

Expected result: apply_discount(200, 10) returns 180.00, and apply_discount(200, 80) returns 100.00 because of the cap.

-- TODO: CREATE FUNCTION apply_discount(price numeric, percent numeric) RETURNS numeric

-- TODO: SELECT apply_discount(200, 10);
-- TODO: SELECT apply_discount(200, 80);

CREATE FUNCTION apply_discount(price NUMERIC, percent NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  effective_percent NUMERIC := LEAST(percent, 50);
BEGIN
  RETURN ROUND(price * (1 - effective_percent / 100), 2);
END;
$$;

SELECT apply_discount(200, 10);
SELECT apply_discount(200, 80);

Quick quiz

Interview questions