Tutoriel PostgreSQL PL/pgSQL

← Retour aux tutoriels | Voir le tutoriel PostgreSQL SQL

Objectif du tutoriel

Cette page reprend les bases de PL/pgSQL pour un profil déjà à l'aise avec Oracle PL/SQL : blocs, variables, fonctions, procédures, curseurs, exceptions et triggers, avec des points de comparaison utiles pour retrouver vite ses repères.

Parcours d'apprentissage

• Niveau 1 : blocs, variables, SELECT INTO, IF, boucles.
• Niveau 2 : fonctions, procédures, curseurs, exceptions.
• Niveau 3 : triggers et adaptation Oracle PL/SQL vers PL/pgSQL.

1. Bloc de base

DO $$
DECLARE
    v_nom TEXT;
    v_salaire NUMERIC;
BEGIN
    SELECT first_name, salary
    INTO v_nom, v_salaire
    FROM employees
    WHERE employee_id = 100;

    RAISE NOTICE 'Nom: %, Salaire: %', v_nom, v_salaire;
EXCEPTION
    WHEN no_data_found THEN
        RAISE NOTICE 'Employé introuvable';
    WHEN OTHERS THEN
        RAISE NOTICE 'Erreur: %', SQLERRM;
END $$;

Le parallèle Oracle est direct : DECLARE, BEGIN, EXCEPTION restent vos repères. La sortie de debug passe souvent par RAISE NOTICE au lieu de DBMS_OUTPUT.PUT_LINE.

2. Variables et types

DO $$
DECLARE
    v_nombre INTEGER := 10;
    v_texte TEXT := 'Hello';
    v_date TIMESTAMP := CURRENT_TIMESTAMP;
    v_nom employees.first_name%TYPE;
    v_ligne employees%ROWTYPE;
BEGIN
    SELECT *
    INTO v_ligne
    FROM employees
    WHERE employee_id = 100;

    v_nom := v_ligne.first_name;
    RAISE NOTICE 'Nom: %', v_nom;
END $$;

Les ancres %TYPE et %ROWTYPE existent aussi en PL/pgSQL, ce qui facilite beaucoup la transition depuis Oracle.

3. Conditions et boucles

DO $$
DECLARE
    v_salaire NUMERIC := 5000;
    v_bonus NUMERIC;
    i INTEGER;
BEGIN
    IF v_salaire > 10000 THEN
        v_bonus := v_salaire * 0.15;
    ELSIF v_salaire > 5000 THEN
        v_bonus := v_salaire * 0.10;
    ELSE
        v_bonus := v_salaire * 0.05;
    END IF;

    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Iteration %', i;
    END LOOP;

    WHILE v_bonus < 1000 LOOP
        v_bonus := v_bonus + 100;
    END LOOP;
END $$;

4. Fonctions

CREATE OR REPLACE FUNCTION fn_bonus(p_salaire NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_bonus NUMERIC;
BEGIN
    IF p_salaire > 10000 THEN
        v_bonus := p_salaire * 0.15;
    ELSIF p_salaire > 5000 THEN
        v_bonus := p_salaire * 0.10;
    ELSE
        v_bonus := p_salaire * 0.05;
    END IF;

    RETURN v_bonus;
END;
$$;

SELECT fn_bonus(7000);

En PostgreSQL, les fonctions sont très centrales. Historiquement, elles ont souvent été plus utilisées que les procédures.

5. Procédures

CREATE OR REPLACE PROCEDURE maj_salaires(p_dept_id INTEGER, p_taux NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_taux)
    WHERE department_id = p_dept_id;
END;
$$;

CALL maj_salaires(10, 0.05);

Le parallèle Oracle procédure reste naturel. Retenez surtout la syntaxe CALL pour l'exécution d'une procédure.

6. Curseurs et parcours de résultats

DO $$
DECLARE
    emp_rec RECORD;
BEGIN
    FOR emp_rec IN
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 50
    LOOP
        RAISE NOTICE '% - %', emp_rec.first_name, emp_rec.salary;
    END LOOP;
END $$;

Comme en Oracle, vous pouvez ouvrir des curseurs explicites, mais le parcours en boucle FOR ... IN SELECT reste souvent le plus lisible pour les cas courants.

7. Exceptions

DO $$
BEGIN
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division par zéro détectée';
    WHEN OTHERS THEN
        RAISE NOTICE 'Erreur: %', SQLERRM;
END $$;

Le schéma mental reste proche d'Oracle. Vous gagnez à mémoriser quelques noms d'exceptions PostgreSQL, puis à garder un traitement WHEN OTHERS propre pour la journalisation.

8. Triggers

CREATE OR REPLACE FUNCTION trg_factures_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.statut := UPPER(NEW.statut);
    RETURN NEW;
END;
$$;

CREATE TRIGGER before_factures_insert
BEFORE INSERT OR UPDATE ON compta.factures
FOR EACH ROW
EXECUTE FUNCTION trg_factures_audit();

Point important : en PostgreSQL, un trigger appelle une fonction qui retourne souvent TRIGGER. Ce n'est pas exactement le même modèle mental qu'un trigger Oracle, même si l'intention métier est proche.

9. Oracle PL/SQL vers PostgreSQL PL/pgSQL

Oracle PL/SQL                    PostgreSQL PL/pgSQL
------------------------------   ---------------------------------------------
DBMS_OUTPUT.PUT_LINE             RAISE NOTICE
Bloc DECLARE/BEGIN/EXCEPTION     bloc DO $$ ... $$ ou fonction/procedure
SELECT ... INTO                  SELECT ... INTO
%TYPE / %ROWTYPE                 %TYPE / %ROWTYPE
Procedure appelee directement    CALL ma_procedure(...)
Fonction dans package            fonction schéma.nom(...)
Package                          pas d'équivalent direct, souvent schéma + fonctions
Trigger PL/SQL                   trigger + trigger function
NO_DATA_FOUND                    exception équivalente selon le contexte
SQLERRM                          SQLERRM

Le point clé pour un expert Oracle : vous ne retrouverez pas le concept de package de la même manière. En PostgreSQL, on organise plus souvent par schémas, conventions de nommage et groupes de fonctions.

10. Pièges fréquents

• Package Oracle : il n'y a pas de copie conforme en PostgreSQL.
• Debug : utilisez RAISE NOTICE au lieu de chercher un équivalent exact de DBMS_OUTPUT.
• Trigger : la fonction de trigger doit retourner NEW ou OLD selon le cas.
• Transaction : gardez en tête la différence entre fonction, procédure et contexte d'appel.
• Organisation : remplacez le réflexe package par schéma + fonction + documentation claire.

Liens utiles dans le site

Voir aussi le tutoriel Oracle PL/SQL, le tutoriel Oracle SQL, le tutoriel PostgreSQL SQL et le tutoriel PostgreSQL avancé et administration.