Write Reusable and Reliable Code with SQL in PostgreSQL

Table of Contents

1. Module 1 – Encapsulation of business logic with User-defined Functions

• 1.1 Reusable code and reliable code: definitions
• 1.2 User-defined Functions (UDFs): fundamental concepts
• 1.3 Demo: Setting up the development environment
• 1.4 Demo: Understanding and creating UDFs
• 1.5 Demo: Management of variables and expressions in functions
• 1.6 Demo: Reusing logic and calling functions in queries
• 1.7 Demo: Organization and documentation of functions for maintainability

2. Module 2 – Enhancing Features with Control Structures and Conditional Logic

• 2.1 Introduction to control structures and conditional logic
• 2.2 Demo: Conditional logic with IF and CASE
• 2.3 Demo: Iteration with FOR loops and Cursors
• 2.4 Demo: Error handling and exception blocks
• 2.5 Demo: Secure execution of dynamic SQL
• 2.6 Demo: Returning records and result sets

3. – Securing and Managing Functions in Multi User Environments

• 3.1 Introduction: function security in PostgreSQL
• 3.2 Demo: SECURITY INVOKER vs SECURITY DEFINER
• 3.3 Demo: Access restriction and permissions management
• 3.4 Demo: Visibility control with schemas and search_path
• 3.5 Demo: Audit and versioning of function changes

4. Module 4 – Best practices for maintainable SQL code

• 4.1 Introduction: why readability conventions matter
• 4.2 Demo: Consistent naming and commenting standards
• 4.3 Demo: Structuring queries for clarity and aliases
• 4.4 Demo: Testing functions with datasets
• 4.5 Demo: Performance Optimization and Execution Plans

5. Demo Files (Quick Reference)

1. – Encapsulating business logic with User defined Functions

1.1 Reusable code and reliable code: definitions

The majority of SQL problems do not stem from missing functionality. They come from logic that is copied, slightly modified, and then gradually drifts in different directions. PostgreSQL offers a solution to this problem with user-defined functions (UDFs).

Reusable code

Reusable code refers to SQL logic written once and used consistently across queries, reports, and applications. Its main advantages are:

• Single source of truth: Business rules reside in one place, not copied everywhere.
• Ease of reuse: functions can be called from queries, views and applications. The logic is written once and then called each time without rewriting the same joins, filters, and calculations.
• Simplified maintenance: modifications are made only once and apply everywhere. When a business rule changes, we update the function and the entire system follows in a predictable manner.

Safe code

Reliable code refers to SQL logic that produces correct and predictable results even as data and usage increase. Its key features are:

• Consistent results: the same inputs always produce the same outputs.
• Safer execution: With structured logic and built-in checks, we reduce errors before they become problems in production.
• Production Ready: Designed to handle real-world workloads and future developments.

1.2 User-defined Functions (UDFs): fundamental concepts

User-defined functions encapsulate SQL logic in reusable blocks that accept parameters and return results. They allow business rules to be centralized directly in the database.

What UDFs allow in PostgreSQL

1. Reusable SQL: Write complex logic once and call it wherever consistent results are needed.
2. Conditional and procedural logic: use IF, CASE, loops and expressions to build smarter behavior. This is where SQL starts to feel less like a calculator and more like a small decision engine.
3. Centralized logic: Keep critical logic close to the data for easier updates and safer execution, with less risk of drift.

Fundamental pattern of any UDF

Each user-defined function follows the same basic pattern:

1. Input: The function receives values ​​from queries or applications at runtime.
2. Logic: it applies calculations, rules and transformations to data.
3. Results: It returns a value, a row or a complete set of results to the caller.

Constituent elements of a PostgreSQL function

CREATE OR REPLACE FUNCTION nom_fonction(
    parametre1 TYPE,
    parametre2 TYPE
)
RETURNS TYPE_RETOUR
LANGUAGE sql | plpgsql
AS $$
    -- corps de la fonction
$$;

• Function name: describes what the function does. The caller should not have to know how it works internally.
• Parameters: the function inputs. Allows you to pass values ​​at run time.
• Return type: tells PostgreSQL what type of result the function will produce (number, text, row, result set).
• Function body: where the actual SQL logic lives.
• Language: sql for simple and straightforward logic; plpgsql for procedural logic.

1.3 Demo: Setting up the development environment

File: 01/demos/1-setup.sql

The development environment uses pgAdmin with fresh SQL script. The first step is to create a dedicated schema to isolate the demos.

Creating the schema and configuring the search_path

-- Création d'un schema dédié pour les démos de fonctions
CREATE SCHEMA IF NOT EXISTS business_logic;
SET search_path TO business_logic, public;

The business_logic schema provides a safe and isolated space where all demos can coexist. If the schema already exists, PostgreSQL simply moves on, without error.

Basic tables

-- Table customers
CREATE TABLE IF NOT EXISTS customers (
    customer_id   SERIAL PRIMARY KEY,
    full_name     TEXT        NOT NULL,
    email         TEXT        UNIQUE NOT NULL
);

-- Table orders
CREATE TABLE IF NOT EXISTS orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INTEGER     NOT NULL,
    order_date    DATE        NOT NULL DEFAULT current_date,
    order_amount  NUMERIC(10,2) NOT NULL CHECK (order_amount >= 0),
    status        TEXT        NOT NULL DEFAULT 'placed'
);

The orders table has a CHECK constraint: the amount must be greater than or equal to 0. This is a small but important step towards safer execution.

Reset and seed data

-- Réinitialisation pour un état propre à chaque exécution
TRUNCATE TABLE customers RESTART IDENTITY;
TRUNCATE TABLE orders RESTART IDENTITY;

-- Insertion de 5 clients
INSERT INTO customers (full_name, email)
VALUES
('John Doe',        'john.doe@example.com'),
('Jane Smith',      'jane.smith@example.com'),
('Mike Johnson',    'mike.johnson@example.com'),
('Emily Davis',     'emily.davis@example.com'),
('Robert Brown',    'robert.brown@example.com');

-- Insertion de 5 commandes (statuts variés : paid, placed, cancelled)
INSERT INTO orders (customer_id, order_date, order_amount, status)
VALUES
(1, '2025-01-10', 120.00, 'paid'),
(2, '2025-01-12',  75.50, 'placed'),
(3, '2025-01-15', 240.00, 'paid'),
(4, '2025-01-18',  50.00, 'cancelled'),
(5, '2025-01-20', 180.75, 'paid');

Why use TRUNCATE with RESTART IDENTITY? Predictable data leads to predictable behavior, which is exactly what reliable code relies on. Each execution of the script gives the same IDs and the same data.

1.4 Demo: Understanding and creating UDFs

File: 01/demos/2-fnfirst.sql

Before functions: repeated logic

Without UDFs, the same calculation logic is written in multiple queries, reports, and applications. Everything works at first, but small changes start to creep in. Over time, two queries that were supposed to mean the same thing start returning slightly different results.

After functions: centralized logic

With UDFs, logic is written once in a function definition. Queries remain shorter, easier to read, and much easier to trust.

Creating a first SQL function

-- Avant : logique directe dans la requête
SELECT order_amount
FROM orders
WHERE order_id = 1;

-- Après : même logique encapsulée dans une fonction
CREATE OR REPLACE FUNCTION get_order_amount(
    p_order_id INTEGER
)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT order_amount
    FROM orders
    WHERE order_id = p_order_id;
$$;

-- Appel de la fonction
SELECT get_order_amount(1);

The output does not change (still 120), but the way of producing it changes. The logic now resides in one place, and every caller gets the same consistent result.

Creating a PL/pgSQL function

CREATE OR REPLACE FUNCTION get_order_status(
    p_order_id INTEGER
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT status
        FROM orders
        WHERE order_id = p_order_id
    );
END;
$$;

-- Appel
SELECT get_order_status(1);  -- retourne 'paid'

plpgsql is different from the sql language because it supports procedural logic: BEGIN/END blocks, explicit return instructions, conditions and control flow.

Using both functions together in a query

SELECT
    order_id,
    get_order_amount(order_id) AS order_amount,
    get_order_status(order_id) AS order_status
FROM orders
ORDER BY order_id;

The result shows 5 rows: each row includes the order_id, the correct amount and the correct status. The query is easy to read, the logic is not duplicated, the results are consistent on each line.

1.5 Demo: Managing variables and expressions in functions

File: 01/demos/3-fnvars.sql

Why use variables

Without variables: expressions become difficult to read, intermediate results disappear as soon as they are calculated, all logic is enclosed in a single instruction.

With variables: values ​​can be stored and reused, logic becomes clearer and more readable, functions can respond dynamically to different inputs as execution progresses.

Variable mechanism in PL/pgSQL

1. Declaration: variables are declared in the DECLARE section. This is where we define which values ​​we want to store and their type.
2. Assignment: values ​​are assigned with :=. Variables can be updated and reused instead of recalculating the same expressions.
3. Scope: variables are only accessible inside the function. Once execution is complete, they are automatically deleted.

Practical example: calculating an amount with discount

CREATE OR REPLACE FUNCTION calculate_discounted_amount(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_amount      NUMERIC;   -- montant original de la commande
    v_discounted_amount NUMERIC;   -- montant après remise
BEGIN
    -- Étape 1 : récupérer le montant une seule fois
    SELECT order_amount
    INTO v_order_amount
    FROM orders
    WHERE order_id = p_order_id;

    -- Étape 2 : calculer la remise avec une expression claire
    v_discounted_amount := v_order_amount * (1 - p_discount_rate);

    -- Étape 3 : retourner le résultat
    RETURN v_discounted_amount;
END;
$$;

-- Appel avec différentes valeurs de remise
SELECT calculate_discounted_amount(1, 0.10);  -- remise 10%
SELECT calculate_discounted_amount(1, 0.25);  -- remise 25%

-- Utilisation dans une requête
SELECT
    order_id,
    order_amount,
    calculate_discounted_amount(order_id, 0.15) AS discounted_amount
FROM orders
ORDER BY order_id;

The function is flexible, readable and predictable. There are no hidden calculations and no repeated expressions.

1.6 Demo: Reusing logic and calling functions in queries

File: 01/demos/4-fnreuse.sql

Why function reuse is important

• Logic defined only once: When business rules live in a single function, they stop propagating across queries, reports, and applications.
• Simpler, shorter queries: Instead of long expressions and repeated calculations, the query focuses on intent.
• Changes applied consistently everywhere: Update the function only once, and each query that calls it automatically follows the new rule.

Layered logic: functions that call other functions

-- Fonction de base : récupère le montant de la commande
CREATE OR REPLACE FUNCTION get_order_amount(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT order_amount FROM orders WHERE order_id = p_order_id;
$$;

-- Fonction intermédiaire : appelle get_order_amount et applique la remise
CREATE OR REPLACE FUNCTION calculate_discounted_amount(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount NUMERIC;
BEGIN
    v_amount := get_order_amount(p_order_id);  -- appel d'une autre fonction
    RETURN v_amount * (1 - p_discount_rate);
END;
$$;

-- Fonction de haut niveau : s'appuie sur calculate_discounted_amount
CREATE OR REPLACE FUNCTION calculate_tax_amount(
    p_order_id INTEGER,
    p_tax_rate  NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_discounted_amount NUMERIC;
BEGIN
    v_discounted_amount := calculate_discounted_amount(p_order_id, 0.10);
    RETURN v_discounted_amount * p_tax_rate;
END;
$$;

Using layered functions in a query

SELECT
    order_id,
    get_order_amount(order_id)              AS order_amount,
    calculate_discounted_amount(order_id, 0.10) AS discounted_amount,
    calculate_tax_amount(order_id, 0.08)    AS tax_amount
FROM orders
ORDER BY order_id;

Each function has a single responsibility, and they build on each other. The query remains readable even if the logic behind it is not trivial.

1.7 Demo: Organization and documentation of functions for maintainability

File: 01/demos/5-fndoc.sql

Organization of functions with schemas

Schemas allow you to group related functions together. This separates business logic from tables and other objects, and keeps the database structure intentional rather than accidental.

-- Deux schemas dédiés avec des règles différentes
CREATE SCHEMA IF NOT EXISTS business_functions;  -- logique de production
CREATE SCHEMA IF NOT EXISTS sandbox_functions;   -- logique de test/comparaison

Function naming conventions

Key rules:

• Use action-oriented names: calculate_, get_, apply_, validate_
• Avoid unclear abbreviations
• Maintain consistent names between schemas

Same function name in different schemas

-- Version production : applique le taux de remise tel quel
CREATE OR REPLACE FUNCTION business_functions.calculate_discounted_amount(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE v_amount NUMERIC;
BEGIN
    SELECT order_amount INTO v_amount
    FROM business_logic.orders WHERE order_id = p_order_id;
    RETURN v_amount * (1 - p_discount_rate);
END;
$$;

-- Version sandbox : applique des contraintes min/max sur le taux
CREATE OR REPLACE FUNCTION sandbox_functions.calculate_discounted_amount(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount        NUMERIC;
    v_effective_rate NUMERIC;
BEGIN
    SELECT order_amount INTO v_amount
    FROM business_logic.orders WHERE order_id = p_order_id;

    -- Règle différente : remise minimum 5%, maximum 30%
    v_effective_rate := GREATEST(p_discount_rate, 0.05);
    v_effective_rate := LEAST(v_effective_rate, 0.30);

    RETURN v_amount * (1 - v_effective_rate);
END;
$$;

Documentation with COMMENT ON FUNCTION

COMMENT ON FUNCTION business_functions.calculate_discounted_amount(INTEGER, NUMERIC)
IS 'Production version. Returns discounted amount using the provided discount rate.';

COMMENT ON FUNCTION sandbox_functions.calculate_discounted_amount(INTEGER, NUMERIC)
IS 'Sandbox version. Returns discounted amount using a rate clamped between 5% and 30%.';

Calling both versions side by side

SELECT
    o.order_id,
    o.order_amount,
    business_functions.calculate_discounted_amount(o.order_id, 0.02) AS prod_discounted_amount,
    sandbox_functions.calculate_discounted_amount(o.order_id, 0.02)  AS sandbox_discounted_amount,
    (business_functions.calculate_discounted_amount(o.order_id, 0.02)
     - sandbox_functions.calculate_discounted_amount(o.order_id, 0.02)) AS difference
FROM business_logic.orders o
ORDER BY o.order_id;

Even though the past discount rate is 2%, the sandbox version applies the 5% minimum rule and produces a different result. The production version does not do this. Schemas are not just records: they control behavior and make intent explicit.

Viewing documentation via system catalogs

SELECT
    n.nspname   AS schema_name,
    p.proname   AS function_name,
    pg_get_function_identity_arguments(p.oid) AS signature,
    obj_description(p.oid) AS documentation
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname IN ('business_functions', 'sandbox_functions')
  AND p.proname = 'calculate_discounted_amount'
ORDER BY n.nspname, p.proname;

2. – Enhancing Functionality with Control Structures and Conditional Logic

2.1 Introduction to Control Structures and Conditional Logic

Control structures

Control structures help guide how a function executes: they decide which instructions execute, when they execute, and how many times they execute based on conditions or rules. Instead of SQL always following the same straight path, control structures allow logic to branch and react.

Conditional logic

Conditional logic allows a function to choose different paths at runtime rather than always performing the same steps. The same function may behave differently across values, ranges, or states.

Why this is important in SQL

Business rules are rarely applied uniformly:

• Apply different discounts depending on order values
• Validate inputs before processing
• Choose a behavior according to status or category
• Handle edge cases without duplicating code everywhere

IF vs CASE in PL/pgSQL

2.2 Demo: Conditional logic with IF and CASE

File: 02/demos/1-ifcase.sql

Function with IF and ELSIF (execution logic connected)

CREATE OR REPLACE FUNCTION get_discount_rate_if(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount   NUMERIC;
    v_status   TEXT;
    v_discount NUMERIC;
BEGIN
    SELECT order_amount, status
    INTO v_amount, v_status
    FROM orders
    WHERE order_id = p_order_id;

    -- Validation : commande introuvable
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % not found', p_order_id;
    END IF;

    -- Logique de remise basée sur le statut et le montant
    IF v_status = 'cancelled' THEN
        v_discount := 0.00;

    ELSIF v_status = 'paid' AND v_amount >= 200 THEN
        v_discount := 0.15;      -- haute valeur payée

    ELSIF v_status = 'paid' THEN
        v_discount := 0.10;      -- payée standard

    ELSIF v_status = 'placed' AND v_amount >= 150 THEN
        v_discount := 0.07;      -- placée haute valeur

    ELSIF v_status = 'placed' THEN
        v_discount := 0.05;      -- placée standard

    ELSE
        v_discount := 0.00;
    END IF;

    RETURN v_discount;
END;
$$;

• IF NOT FOUND is a PL/pgSQL feature that indicates whether the SELECT returned a row.
• The IF/ELSIF string decides the execution path based on both status and amount.

Function with CASE (value mapping)

CREATE OR REPLACE FUNCTION get_order_tier_case(p_order_id INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount NUMERIC;
    v_status TEXT;
    v_tier   TEXT;
BEGIN
    SELECT order_amount, status
    INTO v_amount, v_status
    FROM orders
    WHERE order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % not found', p_order_id;
    END IF;

    -- CASE expression : assigne une valeur selon les conditions
    v_tier :=
        CASE
            WHEN v_status = 'cancelled'              THEN 'Cancelled'
            WHEN v_status = 'paid' AND v_amount >= 200 THEN 'Premium'
            WHEN v_status = 'paid'                   THEN 'Standard'
            WHEN v_status = 'placed'                 THEN 'Pending'
            ELSE 'Other'
        END;

    RETURN v_tier;
END;
$$;

The key difference: CASE assigns v_tier via an expression. The execution path does not change. Only the returned value changes.

Using both functions together

SELECT
    o.order_id,
    o.order_amount,
    o.status,
    get_discount_rate_if(o.order_id)                                       AS discount_rate,
    (o.order_amount * (1 - get_discount_rate_if(o.order_id)))::NUMERIC(10,2) AS discounted_amount,
    get_order_tier_case(o.order_id)                                        AS order_tier
FROM orders o
ORDER BY o.order_id;

2.3 Demo: Iteration with FOR loops and Cursors

File: 02/demos/2-forcursor.sql

Log table to view processing line by line

CREATE TABLE IF NOT EXISTS order_processing_log (
    log_id     SERIAL PRIMARY KEY,
    method     TEXT      NOT NULL,
    order_id   INTEGER   NOT NULL,
    note       TEXT      NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

FOR loop: automatic iteration over query results

CREATE OR REPLACE FUNCTION process_orders_for_loop(p_status TEXT)
RETURNS TABLE (
    processed_count INTEGER,
    processed_total NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    r_order RECORD;
    v_count INTEGER := 0;
    v_total NUMERIC := 0;
BEGIN
    FOR r_order IN
        SELECT order_id, order_amount
        FROM orders
        WHERE status = p_status
        ORDER BY order_id
    LOOP
        v_count := v_count + 1;
        v_total := v_total + r_order.order_amount;

        INSERT INTO order_processing_log(method, order_id, note)
        VALUES ('FOR', r_order.order_id, 'Processed in FOR loop');
    END LOOP;

    processed_count := v_count;
    processed_total := v_total;

    RETURN NEXT;
END;
$$;

-- Exécution
SELECT * FROM process_orders_for_loop('paid');

CURSOR loop: explicit control with OPEN, FETCH, CLOSE

CREATE OR REPLACE FUNCTION process_orders_cursor_loop(p_status TEXT)
RETURNS TABLE (
    processed_count INTEGER,
    processed_total NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    c_orders CURSOR FOR
        SELECT order_id, order_amount
        FROM orders
        WHERE status = p_status
        ORDER BY order_id;

    v_order_id INTEGER;
    v_amount   NUMERIC;
    v_count    INTEGER := 0;
    v_total    NUMERIC := 0;
BEGIN
    OPEN c_orders;      -- ouverture explicite du cursor

    LOOP
        FETCH c_orders INTO v_order_id, v_amount;
        EXIT WHEN NOT FOUND;   -- condition de sortie

        v_count := v_count + 1;
        v_total := v_total + v_amount;

        INSERT INTO order_processing_log(method, order_id, note)
        VALUES ('CURSOR', v_order_id, 'Processed in CURSOR loop');
    END LOOP;

    CLOSE c_orders;     -- fermeture explicite du cursor

    RETURN QUERY SELECT v_count, v_total;
END;
$$;

Comparison FOR loop vs CURSOR loop

2.4 Demo: Error handling and exception blocks

File: 02/demos/3-error.sql

Why error handling is essential

Without error handling, a single execution problem can interrupt the entire execution. A wrong value can stop the entire function. With error handling, failures become controlled and intentional. Functions respond predictably, making them stable and trustworthy in production.

BEGIN and EXCEPTION blocks in PL/pgSQL

BEGIN
    -- Chemin heureux : logique d'exécution normale
    -- S'exécute en premier lors de l'exécution de la fonction
EXCEPTION
    -- Filet de sécurité : s'exécute UNIQUEMENT en cas d'erreur dans BEGIN
    -- Si tout va bien, il n'est jamais touché
    -- Empêche la fonction d'échouer silencieusement ou de planter
END;

Think of BEGIN as the happy path and EXCEPTION as the emergency exit.

RAISE EXCEPTION: immediate stop with message

CREATE OR REPLACE FUNCTION apply_discount_strict(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount NUMERIC;
    v_status TEXT;
BEGIN
    SELECT order_amount, status
    INTO v_amount, v_status
    FROM orders
    WHERE order_id = p_order_id;

    -- Validation 1 : commande introuvable
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % not found', p_order_id;
    END IF;

    -- Validation 2 : taux NULL
    IF p_discount_rate IS NULL THEN
        RAISE EXCEPTION 'Discount rate cannot be NULL';
    END IF;

    -- Validation 3 : taux hors plage autorisée
    IF p_discount_rate < 0 OR p_discount_rate > 0.50 THEN
        RAISE EXCEPTION 'Invalid discount rate: %. Allowed range is 0 to 0.50', p_discount_rate;
    END IF;

    -- Validation 4 : commande annulée
    IF v_status = 'cancelled' THEN
        RAISE EXCEPTION 'Cancelled orders cannot be discounted. Order % is cancelled', p_order_id;
    END IF;

    RETURN (v_amount * (1 - p_discount_rate))::NUMERIC(10,2);
END;
$$;

EXCEPTION block with WHEN OTHERS: secure function with fallback

CREATE TABLE IF NOT EXISTS function_error_log (
    log_id        SERIAL PRIMARY KEY,
    function_name TEXT      NOT NULL,
    input_context TEXT      NOT NULL,
    error_message TEXT      NOT NULL,
    logged_at     TIMESTAMP NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION apply_discount_safe(
    p_order_id      INTEGER,
    p_discount_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_result NUMERIC;
BEGIN
    BEGIN
        v_result := apply_discount_strict(p_order_id, p_discount_rate);
        RETURN v_result;

    EXCEPTION
        WHEN OTHERS THEN
            -- Enregistrement de l'erreur dans le log
            INSERT INTO function_error_log(function_name, input_context, error_message)
            VALUES (
                'apply_discount_safe',
                format('order_id=%s, discount_rate=%s', p_order_id, p_discount_rate),
                SQLERRM    -- variable contenant le message d'erreur
            );

            -- Avertissement sans arrêt de l'exécution
            RAISE WARNING 'Discount failed for order %, returning NULL. Error: %',
                          p_order_id, SQLERRM;
            RETURN NULL;  -- retour sécurisé au lieu d'une exception
    END;
END;
$$;

Error handling tools in PL/pgSQL

2.5 Demo: Secure execution of dynamic SQL

File: 02/demos/4-dynamic.sql

The danger of insecure dynamic SQL

-- ❌ NE PAS FAIRE : injection SQL possible
-- v_sql := 'SELECT * FROM orders WHERE status = ''' || p_status || '''';

Direct concatenation of input values ​​into a SQL string opens the door to SQL injection. This is a critical security vulnerability.

Secure technique: EXECUTE with USING

CREATE OR REPLACE FUNCTION get_orders_dynamic_safe(
    p_status     TEXT    DEFAULT NULL,
    p_min_amount NUMERIC DEFAULT NULL,
    p_sort_col   TEXT    DEFAULT 'order_id',
    p_sort_dir   TEXT    DEFAULT 'ASC'
)
RETURNS TABLE (
    order_id     INTEGER,
    customer_id  INTEGER,
    order_date   DATE,
    order_amount NUMERIC,
    status       TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql      TEXT;
    v_where    TEXT := ' WHERE 1=1 ';
    v_sort_col TEXT;
    v_sort_dir TEXT;
BEGIN
    -- 1. Validation et normalisation de la direction de tri
    IF p_sort_dir IS NULL THEN
        v_sort_dir := 'ASC';
    ELSE
        v_sort_dir := UPPER(p_sort_dir);
    END IF;

    IF v_sort_dir NOT IN ('ASC', 'DESC') THEN
        RAISE EXCEPTION 'Invalid sort direction: %. Use ASC or DESC.', p_sort_dir;
    END IF;

    -- 2. Whitelist des colonnes de tri autorisées (protection contre l'injection)
    IF p_sort_col IN ('order_id', 'customer_id', 'order_date', 'order_amount', 'status') THEN
        v_sort_col := p_sort_col;
    ELSE
        RAISE EXCEPTION 'Invalid sort column: %', p_sort_col;
    END IF;

    -- 3. Construction de la clause WHERE avec des placeholders ($1, $2)
    IF p_status IS NOT NULL THEN
        v_where := v_where || ' AND status = $1 ';
    END IF;

    IF p_min_amount IS NOT NULL THEN
        IF p_status IS NULL THEN
            v_where := v_where || ' AND order_amount >= $1 ';
        ELSE
            v_where := v_where || ' AND order_amount >= $2 ';
        END IF;
    END IF;

    -- 4. Assemblage du SQL avec format() pour les identifiants
    v_sql := 'SELECT order_id, customer_id, order_date, order_amount, status
              FROM business_logic.orders'
             || v_where
             || format(' ORDER BY %I %s', v_sort_col, v_sort_dir);
    --  %I = identifiant sécurisé (avec quote_ident)

    -- 5. Exécution avec USING pour les valeurs (passage sécurisé des paramètres)
    IF p_status IS NOT NULL AND p_min_amount IS NOT NULL THEN
        RETURN QUERY EXECUTE v_sql USING p_status, p_min_amount;
    ELSIF p_status IS NOT NULL THEN
        RETURN QUERY EXECUTE v_sql USING p_status;
    ELSIF p_min_amount IS NOT NULL THEN
        RETURN QUERY EXECUTE v_sql USING p_min_amount;
    ELSE
        RETURN QUERY EXECUTE v_sql;
    END IF;
END;
$$;

Secure dynamic function calls

-- Filtrer par statut uniquement
SELECT * FROM get_orders_dynamic_safe('paid', NULL, 'order_id', 'ASC');

-- Filtrer par montant minimum uniquement
SELECT * FROM get_orders_dynamic_safe(NULL, 100, 'order_amount', 'DESC');

-- Filtrer par les deux
SELECT * FROM get_orders_dynamic_safe('placed', 100, 'order_id', 'ASC');

-- Sans filtre, tri différent
SELECT * FROM get_orders_dynamic_safe(NULL, NULL, 'status', 'ASC');

-- Ceci échoue à cause de la validation whitelist (comportement voulu)
-- SELECT * FROM get_orders_dynamic_safe(NULL, NULL, 'drop table orders', 'ASC');

Principles of Secure Dynamic SQL

2.6 Demo: Returning records and result sets

File: 02/demos/5-returns.sql

Functions are not limited to simple calculations. In PostgreSQL, functions can return:

• A scalar value: a single number, a text string, etc.
• A single record: a single structured line.
• A set of lines: a complete result usable in FROM.

Return a single record with RETURNS TABLE

CREATE OR REPLACE FUNCTION get_order_details(p_order_id INTEGER)
RETURNS TABLE (
    order_id      INTEGER,
    customer_id   INTEGER,
    customer_name TEXT,
    order_date    DATE,
    order_amount  NUMERIC,
    status        TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        o.order_id,
        o.customer_id,
        c.full_name AS customer_name,
        o.order_date,
        o.order_amount,
        o.status
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE o.order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % not found', p_order_id;
    END IF;
END;
$$;

SELECT * FROM get_order_details(1);

Return a set of rows (set-returning function)

CREATE OR REPLACE FUNCTION get_orders_by_status(p_status TEXT)
RETURNS TABLE (
    order_id     INTEGER,
    customer_id  INTEGER,
    order_date   DATE,
    order_amount NUMERIC,
    status       TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        o.order_amount,
        o.status
    FROM orders o
    WHERE o.status = p_status
    ORDER BY o.order_id;

    IF NOT FOUND THEN
        RAISE NOTICE 'No orders found with status %', p_status;
    END IF;
END;
$$;

-- La fonction set-returning s'utilise comme une table
SELECT * FROM get_orders_by_status('paid');
SELECT * FROM get_orders_by_status('placed');

Return mechanisms in PL/pgSQL

3. – Securing and Managing Functions in Multi User Environments

3.1 Introduction: Function security in PostgreSQL

In multi-user systems, functions are not just tools for reuse. They also define security boundaries. When several roles use the same functions, you must check:

• Who can perform which function
• With what permissions does the function run
• Which version of logic is used

3.2 Demo: SECURITY INVOKER vs SECURITY DEFINER

File: 03/demos/1-sec.sql

SECURITY INVOKER (default behavior)

CREATE OR REPLACE FUNCTION app_data.fn_invoker(p_account_id INTEGER)
RETURNS TEXT
LANGUAGE sql
SECURITY INVOKER  -- s'exécute avec les permissions de l'APPELANT
AS $$
    SELECT customer_name
    FROM app_data.customer_accounts
    WHERE account_id = p_account_id;
$$;

• The function runs with the permissions of the caller.
• The caller must have access to all objects used in the function.
• If the caller does not have access to customer_accounts, the function fails.

SECURITY DEFINER

CREATE OR REPLACE FUNCTION app_data.fn_definer(p_account_id INTEGER)
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
SET search_path = app_data, pg_catalog  -- IMPORTANT : toujours définir le search_path
AS $$
    SELECT customer_name
    FROM app_data.customer_accounts
    WHERE account_id = p_account_id;
$$;

• The function runs with the permissions of the function owner.
• The caller can use the function even without direct access to the table.
• Critical Security: always explicitly define the search_path in a SECURITY DEFINER function to avoid schema injection attacks.

Demonstrating the differences

-- Créer un rôle de faible privilège
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'demo_user') THEN
        CREATE ROLE demo_user LOGIN;
    END IF;
END $$;

-- Révoquer tout accès direct à la table
REVOKE ALL ON TABLE app_data.customer_accounts FROM demo_user;

-- Accorder uniquement EXECUTE sur les fonctions
GRANT USAGE ON SCHEMA app_data TO demo_user;
GRANT EXECUTE ON FUNCTION app_data.fn_invoker(INTEGER) TO demo_user;
GRANT EXECUTE ON FUNCTION app_data.fn_definer(INTEGER) TO demo_user;

-- Tester en tant que demo_user
SET ROLE demo_user;

SELECT customer_name FROM app_data.customer_accounts WHERE account_id = 1;
-- ❌ Erreur : permission refusée sur la table

SELECT app_data.fn_invoker(1);
-- ❌ Erreur : permission refusée (SECURITY INVOKER utilise les permissions de l'appelant)

SELECT app_data.fn_definer(1);
-- ✅ Succès : 'John Doe' (SECURITY DEFINER utilise les permissions du propriétaire)

RESET ROLE;

Checking via system catalog

SELECT
    p.proname   AS function_name,
    p.prosecdef AS is_security_definer
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'app_data'
  AND p.proname IN ('fn_invoker', 'fn_definer')
ORDER BY p.proname;

3.3 Demo: Access restriction and permission management

File: 03/demos/2-grant.sql

Principle of least privilege

Grant only necessary permissions — no more, no less.

-- Retirer l'accès PUBLIC par défaut aux fonctions
REVOKE ALL ON FUNCTION app_data.get_customer_name(INTEGER) FROM PUBLIC;

-- Accorder l'accès au schema (nécessaire)
GRANT USAGE ON SCHEMA app_data TO demo_user;

-- Test sans EXECUTE → échec
SET ROLE demo_user;
SELECT app_data.get_customer_name(1);  -- ❌ Erreur : permission refusée
RESET ROLE;

-- Accorder EXECUTE
GRANT EXECUTE ON FUNCTION app_data.get_customer_name(INTEGER) TO demo_user;

-- Test avec EXECUTE mais sans accès à la table → encore un échec (SECURITY INVOKER)
SET ROLE demo_user;
SELECT app_data.get_customer_name(1);  -- ❌ Erreur : SECURITY INVOKER, pas d'accès table
RESET ROLE;

-- Accorder SELECT sur les colonnes minimales requises
GRANT SELECT (account_id, customer_name)
ON TABLE app_data.customer_accounts
TO demo_user;

-- Test final → succès
SET ROLE demo_user;
SELECT app_data.get_customer_name(1);  -- ✅ Retourne 'John Doe'
RESET ROLE;

Hierarchy of permissions required for SECURITY INVOKER

1. GRANT USAGE ON SCHEMA → access to the schema
2. GRANT EXECUTE ON FUNCTION → right to execute the function
3. GRANT SELECT (columns) ON TABLE → access to the columns used in the function

3.4 Demo: Controlling visibility with schemas and search_path

File: 03/demos/3-search.sql

Deliberate naming conflict between schemas

-- Même nom de fonction dans deux schemas différents
CREATE SCHEMA IF NOT EXISTS app_api;

-- Version dans app_data : retourne les données réelles
CREATE OR REPLACE FUNCTION app_data.get_customer_name(p_account_id INTEGER)
RETURNS TEXT LANGUAGE sql AS $$
    SELECT customer_name
    FROM app_data.customer_accounts
    WHERE account_id = p_account_id;
$$;

-- Version dans app_api : retourne une valeur statique (pour démonstration)
CREATE OR REPLACE FUNCTION app_api.get_customer_name(p_account_id INTEGER)
RETURNS TEXT LANGUAGE sql AS $$
    SELECT 'API version'::TEXT;
$$;

The search_path determines which function runs

-- app_api en premier → version API exécutée
SET search_path TO app_api, app_data, public;
SET ROLE demo_user;
SELECT get_customer_name(1) AS result_with_app_api_first;
-- Résultat : 'API version'
RESET ROLE;

-- app_data en premier → version données exécutée
SET search_path TO app_data, app_api, public;
SET ROLE demo_user;
SELECT get_customer_name(1) AS result_with_app_data_first;
-- Résultat : 'John Doe'
RESET ROLE;

Nothing has changed except the order of the search_path, and yet the behavior is completely different.

Good practice: explicit qualification of the schema

-- Appeler exactement la fonction voulue, sans ambiguïté
SET ROLE demo_user;
SELECT app_api.get_customer_name(1)  AS api_call;
SELECT app_data.get_customer_name(1) AS data_call;
RESET ROLE;

If you want predictable and safe behavior in multi-user systems, organize functions in clear schemas and always qualify important function calls with their schema.

3.5 Demo: Audit and versioning of function changes

File: 03/demos/4-versionaudit.sql

Audit table to monitor the evolution of functions

CREATE TABLE IF NOT EXISTS app_data.function_audit_log (
    audit_id      SERIAL PRIMARY KEY,
    function_name TEXT      NOT NULL,
    version_label TEXT      NOT NULL,
    changed_by    TEXT      NOT NULL,
    changed_at    TIMESTAMP NOT NULL DEFAULT now(),
    change_note   TEXT
);

Versioning workflow: version 1.0

-- Création de la version initiale
CREATE OR REPLACE FUNCTION app_data.get_customer_name(p_account_id INTEGER)
RETURNS TEXT LANGUAGE sql AS $$
    SELECT customer_name
    FROM app_data.customer_accounts
    WHERE account_id = p_account_id;
$$;

-- Documentation de la version
COMMENT ON FUNCTION app_data.get_customer_name(INTEGER)
IS 'Version 1.0 – Initial implementation';

-- Enregistrement dans le log d'audit
INSERT INTO app_data.function_audit_log (function_name, version_label, changed_by, change_note)
VALUES (
    'app_data.get_customer_name(integer)',
    '1.0',
    current_user,    -- qui a fait le changement
    'Initial version'
);

Version 2.0 with logic change

-- Mise à jour avec CREATE OR REPLACE (pas besoin de DROP)
CREATE OR REPLACE FUNCTION app_data.get_customer_name(p_account_id INTEGER)
RETURNS TEXT LANGUAGE sql AS $$
    SELECT customer_name || ' (verified)'
    FROM app_data.customer_accounts
    WHERE account_id = p_account_id;
$$;

COMMENT ON FUNCTION app_data.get_customer_name(INTEGER)
IS 'Version 2.0 – Appends verification label';

INSERT INTO app_data.function_audit_log (function_name, version_label, changed_by, change_note)
VALUES (
    'app_data.get_customer_name(integer)',
    '2.0',
    current_user,
    'Added verification label to output'
);

Viewing audit history

SELECT
    function_name,
    version_label,
    changed_by,
    changed_at,
    change_note
FROM app_data.function_audit_log
ORDER BY changed_at;

Advantages of this pattern

4. – Best Practices for Maintainable SQL

Approximate duration: ~20m

4.1 Introduction: Why readability conventions matter

Well-structured code is easier to read, review and maintain over time. Writing good functions is only half the job. The other half is making them easy to find, understand, and maintain. It’s not about changing the results. It’s about reducing friction for the next person who reads the code — and very often, that next person is you.

4.2 Demo: Consistent naming and commenting standards

File: 04/demos/1-comments.sql

Comments on tables with COMMENT ON TABLE

COMMENT ON TABLE customer_accounts
IS 'Stores core customer information used across reporting and analytics.';

COMMENT ON TABLE customer_orders
IS 'Stores order records associated with customer accounts.';

Comments on columns with COMMENT ON COLUMN

COMMENT ON COLUMN customer_orders.order_status
IS 'Represents the lifecycle state of an order such as NEW, PAID, or CANCELED.';

Viewing comments via system catalogs

-- Commentaires des tables
SELECT
    c.relname     AS table_name,
    d.description AS table_comment
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_description d ON d.objoid = c.oid
WHERE n.nspname = 'app_core'
  AND c.relkind = 'r'
ORDER BY c.relname;

-- Commentaires des colonnes
SELECT
    c.relname     AS table_name,
    a.attname     AS column_name,
    d.description AS column_comment
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum
WHERE n.nspname = 'app_core'
ORDER BY c.relname, a.attname;

Qualities of good function documentation

The documentation must:

• Explain the purpose of the function
• Clearly describe the inputs (parameters)
• Describe what the function returns
• Be brief and to the point (no long essays, no vague placeholders)

The goal: someone should understand why the function exists without reading the entire implementation.

4.3 Demo: Structuring queries for clarity and aliasing

File: 04/demos/2-alias.sql

Poorly structured query: works but difficult to read

SELECT a.customer_name,SUM(o.order_total),COUNT(o.order_id)
FROM customer_accounts a JOIN customer_orders o ON a.account_id=o.account_id
WHERE o.order_status='PAID' AND a.is_active=true
GROUP BY a.customer_name
ORDER BY SUM(o.order_total) DESC;

Everything is compressed into a few lines. Aliases are short but not descriptive. Conditions, joins, groupings and sorting are visually crowded.

Refactored query: same results, maximum clarity

SELECT
    ca.customer_name,
    SUM(co.order_total) AS total_order_amount,
    COUNT(co.order_id)  AS order_count
FROM customer_accounts ca
JOIN customer_orders co
    ON ca.account_id = co.account_id
WHERE ca.is_active = true
  AND co.order_status = 'PAID'
GROUP BY
    ca.customer_name
ORDER BY
    total_order_amount DESC;

The result is the same. What changes is clarity.

Rules for intentional aliases

Formatting rules for logic flow

When formatting follows logic, the query reads like a story, not a puzzle.

4.4 Demo: Testing functions with datasets

File: 04/demos/3-test.sql

Grid test pattern with VALUES

-- Version 1 de la fonction (intentionnellement défectueuse)
-- Bug : ne compte que les commandes PAID, pas toutes
CREATE OR REPLACE FUNCTION get_customer_total_spend(p_account_id INTEGER)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(order_total), 0)
    FROM customer_orders
    WHERE account_id = p_account_id
      AND order_status = 'PAID';  -- Bug : filtre trop restrictif
$$;

-- Grille de tests : résultats actuels vs résultats attendus
SELECT
    p_account_id,
    get_customer_total_spend(p_account_id) AS actual_result,
    expected_result,
    get_customer_total_spend(p_account_id) = expected_result AS test_passed
FROM (
    VALUES
        (1, 200.00),   -- John : 120 PAID + 80 PAID = 200
        (2, 280.00),   -- Jane : 250 PAID + 30 CANCELED = 280 total
        (3, 0.00)      -- Mike (account_id=3 n'existe pas dans les orders)
) AS test_cases(p_account_id, expected_result);

The grid immediately reveals which tests fail.

Function correction (version 2)

-- Version corrigée : inclut toutes les commandes
CREATE OR REPLACE FUNCTION get_customer_total_spend(p_account_id INTEGER)
RETURNS NUMERIC
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(order_total), 0)
    FROM customer_orders
    WHERE account_id = p_account_id;  -- Correction : plus de filtre sur le statut
$$;

-- Re-exécution des mêmes tests → tous doivent passer
SELECT
    p_account_id,
    get_customer_total_spend(p_account_id) AS actual_result,
    expected_result,
    get_customer_total_spend(p_account_id) = expected_result AS test_passed
FROM (
    VALUES
        (1, 200.00),
        (2, 280.00),
        (3, 0.00)
) AS test_cases(p_account_id, expected_result);

Advantages of this test pattern

• Tests in the same tool: no need for an external framework, the tests are written directly in SQL.
• Immediate overview: all columns (actual_result, expected_result, test_passed) in a single result set.
• Reproducible: re-executable each time the function is modified.
• COALESCE: returns 0 instead of NULL for empty accounts — testable behavior.

4.5 Demo: Performance Optimization and Execution Plans

File: 04/demos/4-perf.sql

EXPLAIN: Inspection of the execution plan

-- Inspecter comment PostgreSQL exécute une agrégation
EXPLAIN
SELECT
    SUM(order_total)
FROM customer_orders
WHERE account_id = 1;

EXPLAIN shows the execution plan: how PostgreSQL plans to execute the query (index scan, sequential scan, hash join, etc.).

Comparison: use of an index vs suboptimal pattern

-- ✅ Utilise l'index sur account_id
EXPLAIN
SELECT SUM(order_total)
FROM customer_orders
WHERE account_id = 1;

-- ❌ L'enveloppement de la colonne dans une fonction empêche l'utilisation de l'index
EXPLAIN
SELECT SUM(order_total)
FROM customer_orders
WHERE CAST(account_id AS TEXT) = '2';

When an indexed column is wrapped in a function (CAST, UPPER, LOWER, etc.), PostgreSQL generally cannot use the index and will perform a sequential scan instead.

EXPLAIN ANALYZE: real execution with timings

-- Mesurer le coût d'exécution réel
EXPLAIN ANALYZE
SELECT
    SUM(order_total)
FROM customer_orders
WHERE account_id = 2;

EXPLAIN ANALYZE actually executes the query and reports actual timings. Useful for validating that the estimated plan matches actual behavior.

Indexes created in module 4 setup

-- Index sur account_id pour accélérer les filtres par client
CREATE INDEX customer_orders_account_id_idx
ON customer_orders(account_id);

-- Index sur order_status pour accélérer les filtres par statut
CREATE INDEX customer_orders_status_idx
ON customer_orders(order_status);

Performance best practices for functions

5. Demo Files (Quick Reference)

Module 1 — 01/demos/

Module 2 — 02/demos/

Module 3 — 03/demos/

Module 4 — 04/demos/

Documentation generated from training content and demo files.

Search Terms

write · reusable · reliable · sql · postgresql · databases · function · functions · logic · pgsql · execution · pattern · query · secure · security · conditional · control · documentation · dynamic · exception · schemas · audit · case · comment