Création de fonctions simples : CREATE FUNCTION

Dans PostgreSQL, les fonctions sont un outil super puissant pour automatiser des tâches, créer de la logique métier et rendre le serveur plus intelligent. Imagine les fonctions comme des mini-programmes qui tournent direct dans ta base de données. Elles sont pratiques pour :

• Réutiliser du code. Si tu fais souvent les mêmes requêtes, mets-les dans une fonction et appelle-la quand tu veux.
• Automatiser des tâches. Par exemple, tu veux calculer le salaire des employés selon leurs heures de taf. Une fonction va gérer ça nickel.
• Encapsuler la logique. Ça permet de garder les calculs compliqués côté serveur, comme ça les clients n'ont pas à se prendre la tête avec des requêtes SQL.

Syntaxe générale de CREATE FUNCTION

Voilà à quoi ressemble la structure de base pour créer une fonction :

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Corps de la fonction (logique)
    RETURN résultat;
END;
$$ LANGUAGE plpgsql;

On décortique les parties principales :

CREATE FUNCTION function_name(parameters) :

Ici, tu donnes un nom à ta fonction function_name et tu mets les paramètres (si t'en as besoin).

Les paramètres peuvent avoir un nom et un type de données : my_param INTEGER, another_param TEXT.

RETURNS return_type :

Tu précises ce que ta fonction va renvoyer : une valeur (INTEGER, TEXT, etc.) ou un ensemble de données (TABLE, RECORD).

BEGIN ... END :

Entre ces mots-clés, c'est le "corps" de la fonction, là où toute la magie opère.

RETURN résultat :

Ça renvoie le résultat de la fonction. Fais gaffe : le type du résultat doit correspondre à ce que t'as mis dans RETURNS.

LANGUAGE plpgsql :

On précise qu'on utilise le langage PL/pgSQL. PostgreSQL en gère d'autres, mais là on reste sur celui-là.

Exemple simple : addition de deux nombres

On va créer une fonction qui renvoie la somme de deux entiers.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Maintenant, on l'appelle :

SELECT add_numbers(5, 7); -- Résultat : 12

Qu'est-ce qui se passe ici ?

• La fonction prend deux paramètres a et b de type INT.
• Dans la fonction, on les additionne (a + b) et on renvoie le résultat.
• C'est aussi simple qu'une calculette !

Exemple avec utilisation de variables

Imaginons qu'on a une base de données d'université et qu'on veut savoir combien d'étudiants sont inscrits.

On crée la fonction :

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- On déclare une variable pour stocker le résultat
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- On compte le nombre de lignes dans la table
    RETURN total; -- On renvoie le résultat
END;
$$ LANGUAGE plpgsql;

Appel de la fonction :

SELECT count_students(); -- Disons, résultat : 120

Ce qu'on voit ici :

• On utilise la variable total pour stocker le résultat de la requête SQL.
• La commande SELECT ... INTO met le résultat de la requête dans la variable.

C'est super pratique si tu dois d'abord traiter les données avant de les renvoyer.

Retourner plusieurs valeurs : RETURNS TABLE

Dans l'exemple d'avant, on renvoyait qu'une seule valeur. Mais si ta fonction doit renvoyer un ensemble de données, genre une liste d'étudiants ? C'est là que RETURNS TABLE devient utile.

Exemple :

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Appel de la fonction :

SELECT * FROM get_students();

Résultat possible :

L'intérêt de RETURN QUERY pour exécuter des requêtes dans une fonction

RETURN QUERY permet de renvoyer direct le résultat d'une requête SQL depuis la fonction. Ça évite des étapes intermédiaires et rend les fonctions plus simples.

On va créer une fonction qui renvoie seulement les étudiants actifs :

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Avant d'appeler la fonction get_active_students(), il faut créer la table students et la remplir avec des données de test. Voilà comment faire :

-- On crée la table des étudiants
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- On ajoute quelques enregistrements
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Table :

Maintenant, appel :

SELECT * FROM get_active_students();

Résultat :

Vérifier la validité des données avant d'exécuter

Les fonctions peuvent contenir des vérifs IF pour s'assurer que les données sont bonnes. Par exemple, on peut créer une fonction pour passer un étudiant à l'année suivante seulement s'il a validé tous ses exams.

Exemple :

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- On compte le nombre d'exams validés par l'étudiant
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'passed';

    -- On vérifie la condition
    IF passed_exams < 5 THEN
        RETURN 'L’étudiant n’a pas validé assez d’exams';
    END IF;

    -- On met à jour l’année de l’étudiant
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN 'Étudiant promu !';
END;
$$ LANGUAGE plpgsql;

Erreurs classiques quand tu crées des fonctions

Oubli du type de retour. PostgreSQL veut toujours que tu dises ce que la fonction va renvoyer. Par exemple :

CREATE FUNCTION fail() AS $$ -- Erreur : pas de RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Correction :

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Type de retour qui ne colle pas. Si tu mets RETURNS INT, tu dois renvoyer un nombre. Essayer de renvoyer une chaîne dans ce cas, c'est pas une bonne idée.

Erreur dans les requêtes SQL à l'intérieur de la fonction. Teste toujours tes requêtes avant de les mettre dans une fonction. Le mieux, c'est de les essayer "à la main" dans psql ou pgAdmin.