PostgreSQL (PL/pgSQL) et SQL Server (T-SQL) : les équivalences

PL/pgSQL est le langage procédural de PostgreSQL, T-SQL (Transact-SQL) celui de SQL Server : ils jouent le même rôle que le SQL procédural de MySQL, avec des procédures, des fonctions et des triggers, mais une syntaxe et parfois une logique différentes. Ce chapitre traduit les exemples du cours dans les deux langages, pour que tu saches lire un sujet ou un code d'entreprise écrit pour l'un ou l'autre, et retrouver l'équivalent MySQL.

Pourquoi les connaître

Les sujets récents de l'E7 SLAM sont presque tous écrits pour MySQL, mais celui de 2024 utilisait SQL Server pour chiffrer une colonne (le chiffrement en T-SQL est dans le cours SQL), et rien n'empêche un sujet de donner un trigger T-SQL à lire. En stage et en entreprise, tu croiseras PostgreSQL (version 18 début novembre 2026, la 19 étant annoncée pour le 19 novembre ; une version majeure par an) dans les applications récentes et SQL Server (version 2025) dans les environnements Microsoft. Le principe reste le même partout ; ce sont les mots qui changent.

Le tableau des équivalences

PL/pgSQL : une fonction

-- PostgreSQL 18
CREATE OR REPLACE FUNCTION duree_totale_ticket(p_id_ticket integer)
RETURNS integer
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_total integer;
BEGIN
  SELECT COALESCE(SUM(duree_min), 0) INTO v_total
  FROM intervention
  WHERE id_ticket = p_id_ticket;
  RETURN v_total;
END;
$$;

SELECT id_ticket, titre, duree_totale_ticket(id_ticket) AS minutes FROM ticket;

• Le corps est une chaîne entre $$ : PostgreSQL n'a pas besoin de DELIMITER, parce que les points-virgules du corps sont à l'intérieur de la chaîne.
• LANGUAGE plpgsql dit dans quel langage le corps est écrit (PostgreSQL en accepte d'autres).
• Les variables se déclarent dans une section DECLARE avant BEGIN.
• STABLE correspond à « lit la base, sans la modifier, même résultat dans une même requête ». C'est l'équivalent des caractéristiques de MySQL : IMMUTABLE pour un pur calcul (DETERMINISTIC NO SQL), STABLE pour une lecture (READS SQL DATA), VOLATILE (par défaut) pour le reste.

PL/pgSQL : une procédure

PostgreSQL a des procédures, appelées avec CALL, depuis sa version 11 ; avant, tout s'écrivait avec des fonctions. La clôture d'un ticket :

-- PostgreSQL 18
CREATE OR REPLACE PROCEDURE cloturer_ticket(
  p_id_ticket     integer,
  p_id_technicien integer,
  p_duree_min     smallint,
  p_compte_rendu  varchar
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_statut varchar(10);
BEGIN
  SELECT statut INTO v_statut
  FROM ticket
  WHERE id_ticket = p_id_ticket
  FOR UPDATE;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Ticket inconnu';
  ELSIF v_statut = 'resolu' THEN
    RAISE EXCEPTION 'Ticket déjà résolu';
  END IF;

  INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
  VALUES (p_id_ticket, p_id_technicien, now(), p_duree_min, p_compte_rendu);

  UPDATE ticket SET statut = 'resolu', date_cloture = now()
  WHERE id_ticket = p_id_ticket;
END;
$$;

CALL cloturer_ticket(6, 12, 0::smallint, 'Ventilateur remplacé');
-- ERROR:  new row for relation "intervention" violates check constraint "ck_intervention_duree"

Il n'y a ni gestionnaire, ni START TRANSACTION, ni ROLLBACK, et pourtant la procédure est atomique : en PostgreSQL, une erreur non rattrapée annule toute l'instruction en cours, ici le CALL. Le ticket 6 reste « en cours ». FOUND est une variable automatique, vraie si la dernière requête a trouvé au moins une ligne : c'est plus sûr que de tester la variable à NULL. Note aussi ELSIF, et 0::smallint, la conversion explicite qu'exige PostgreSQL, plus strict que MySQL sur les types des paramètres.

Une erreur se rattrape avec un bloc EXCEPTION, l'équivalent du gestionnaire EXIT de MySQL :

BEGIN
  INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (p_id_poste, p_id_logiciel, current_date);
EXCEPTION
  WHEN unique_violation THEN
    RAISE EXCEPTION 'Ce logiciel est déjà installé sur ce poste';
END;

Les erreurs ont des noms (unique_violation, foreign_key_violation, check_violation) en plus de leur SQLSTATE ; WHEN OTHERS rattrape tout, comme SQLEXCEPTION.

PL/pgSQL : un trigger

Un trigger PostgreSQL se fait en deux temps : une fonction qui renvoie le type spécial trigger, puis le trigger qui l'exécute.

-- PostgreSQL 18 : 1. la fonction
CREATE OR REPLACE FUNCTION f_ticket_audit_statut()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_ticket (id_ticket, ancien_statut, nouveau_statut, modifie_par)
  VALUES (OLD.id_ticket, OLD.statut, NEW.statut, session_user);
  RETURN NULL;          -- valeur ignorée pour un trigger AFTER
END;
$$;

-- 2. le trigger
CREATE OR REPLACE TRIGGER trg_ticket_audit_statut
AFTER UPDATE OF statut ON ticket
FOR EACH ROW
WHEN (OLD.statut IS DISTINCT FROM NEW.statut)
EXECUTE FUNCTION f_ticket_audit_statut();

• AFTER UPDATE OF statut ne se déclenche que si la colonne statut figure dans l'UPDATE, et la clause WHEN filtre avant même d'appeler la fonction. En MySQL, ce test s'écrit dans le corps avec un IF.
• IS DISTINCT FROM est la comparaison qui tient compte de NULL, l'équivalent de NOT (a <=> b).
• session_user est le compte connecté, l'équivalent de USER() ; current_user est, comme CURRENT_USER() en MySQL, le compte dont les droits s'appliquent.
• Une fonction de trigger BEFORE doit renvoyer NEW (la ligne à écrire, éventuellement modifiée par NEW.titre := trim(NEW.titre);). Si elle renvoie NULL, la ligne est ignorée sans erreur : un piège classique.
• Un même trigger peut réagir à plusieurs événements (BEFORE INSERT OR UPDATE), et FOR EACH STATEMENT (la valeur par défaut) déclenche une seule fois par instruction.
• En PostgreSQL, une fonction de trigger s'exécute par défaut avec les droits de l'utilisateur qui modifie la table, pas avec ceux de son propriétaire : c'est l'inverse de MySQL. Pour qu'un compte applicatif sans droit sur audit_ticket déclenche quand même l'écriture de la trace, déclare la fonction SECURITY DEFINER (et fixe son search_path, comme pour toute fonction SECURITY DEFINER).

Le contrôle du technicien, en BEFORE INSERT :

-- PostgreSQL 18
CREATE OR REPLACE FUNCTION f_intervention_technicien()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NOT EXISTS (SELECT 1
                 FROM employe e
                 JOIN service s ON s.id_service = e.id_service
                 WHERE e.id_employe = NEW.id_technicien AND s.code = 'INFO') THEN
    RAISE EXCEPTION 'Seul un membre du service informatique peut intervenir';
  END IF;
  RETURN NEW;           -- BEFORE : la ligne à écrire
END;
$$;

CREATE OR REPLACE TRIGGER trg_intervention_technicien
BEFORE INSERT ON intervention
FOR EACH ROW
EXECUTE FUNCTION f_intervention_technicien();

T-SQL : une procédure

-- SQL Server (T-SQL)
CREATE OR ALTER PROCEDURE dbo.cloturer_ticket
  @id_ticket     INT,
  @id_technicien INT,
  @duree_min     SMALLINT,
  @compte_rendu  VARCHAR(255)
AS
BEGIN
  SET NOCOUNT ON;       -- pas de message « n lignes affectées » pour chaque requête
  SET XACT_ABORT ON;    -- toute erreur annule la transaction

  BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @statut VARCHAR(10);
    SELECT @statut = statut
    FROM dbo.ticket WITH (UPDLOCK)        -- l'équivalent de FOR UPDATE
    WHERE id_ticket = @id_ticket;

    IF @statut IS NULL
      THROW 50001, N'Ticket inconnu', 1;
    IF @statut = 'resolu'
      THROW 50002, N'Ticket déjà résolu', 1;

    INSERT INTO dbo.intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
    VALUES (@id_ticket, @id_technicien, SYSDATETIME(), @duree_min, @compte_rendu);

    UPDATE dbo.ticket
    SET statut = 'resolu', date_cloture = SYSDATETIME()
    WHERE id_ticket = @id_ticket;

    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;              -- relance l'erreur d'origine, comme RESIGNAL
  END CATCH;
END;
GO

EXEC dbo.cloturer_ticket @id_ticket = 6, @id_technicien = 12, @duree_min = 25,
                         @compte_rendu = 'Ventilateur remplacé';

À retenir pour lire du T-SQL :

• les variables et les paramètres commencent par @ ; il n'y a pas de IN ni de mode, sauf OUTPUT pour un paramètre de sortie ;
• BEGIN TRY … END TRY BEGIN CATCH … END CATCH remplace le gestionnaire d'erreur ; le CATCH annule et relance avec THROW; sans paramètre, exactement comme ROLLBACK; RESIGNAL; ;
• THROW prend un numéro d'au moins 50 000, un message et un « état » de 0 à 255. L'instruction qui précède un THROW doit se terminer par un point-virgule ;
• @@TRANCOUNT compte les transactions ouvertes : on n'annule que s'il y en a une ;
• GO n'est pas du SQL : c'est le séparateur de lots de SSMS et de sqlcmd, le rôle que joue DELIMITER dans le client mysql. CREATE PROCEDURE doit être la première instruction de son lot.

Un paramètre de sortie se déclare OUTPUT des deux côtés :

-- SQL Server (T-SQL)
CREATE OR ALTER PROCEDURE dbo.charge_technicien
  @id_technicien INT,
  @nb_interventions INT OUTPUT,
  @minutes INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT @nb_interventions = COUNT(*), @minutes = COALESCE(SUM(duree_min), 0)
  FROM dbo.intervention
  WHERE id_technicien = @id_technicien;
END;
GO

DECLARE @nb INT, @min INT;
EXEC dbo.charge_technicien @id_technicien = 11, @nb_interventions = @nb OUTPUT, @minutes = @min OUTPUT;
SELECT @nb AS interventions, @min AS minutes;

T-SQL : un trigger par instruction

C'est la différence qui piège le plus : un trigger SQL Server s'exécute une fois par instruction, quel que soit le nombre de lignes, et voit toutes les lignes concernées dans deux tables virtuelles : inserted (les nouvelles valeurs) et deleted (les anciennes). Il n'y a ni FOR EACH ROW, ni NEW, ni OLD. Le chapitre aperçu du cours SQL en donne la version d'audit ; voici le contrôle du technicien :

-- SQL Server (T-SQL)
CREATE OR ALTER TRIGGER dbo.trg_intervention_technicien
ON dbo.intervention
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1
             FROM inserted i
             JOIN dbo.employe e ON e.id_employe = i.id_technicien
             JOIN dbo.service s ON s.id_service = e.id_service
             WHERE s.code <> 'INFO')
    THROW 50003, N'Seul un membre du service informatique peut intervenir', 1;
END;
GO

• La condition porte sur toutes les lignes insérées (FROM inserted) : un INSERT de dix interventions est refusé si une seule ne va pas.
• Il n'y a pas de trigger BEFORE : on contrôle AFTER, et l'erreur annule l'instruction. Dans un trigger, XACT_ABORT est actif par défaut : le THROW annule toute la transaction.
• Un trigger INSTEAD OF remplace l'instruction : il reçoit les lignes dans inserted et fait lui-même l'INSERT, corrigé, s'il le décide. C'est ainsi qu'on « modifie NEW » en T-SQL.

Traduire un trigger FOR EACH ROW en trigger par instruction. La méthode : remplace chaque NEW.colonne par une colonne de inserted, chaque OLD.colonne par une colonne de deleted, joins les deux sur la clé primaire pour un UPDATE, et transforme le IF … INSERT d'une ligne en INSERT … SELECT … WHERE sur l'ensemble des lignes.

Exercices

CREATE OR REPLACE FUNCTION f_ticket_cloture()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.statut = 'resolu' AND OLD.statut <> 'resolu' THEN
    NEW.date_cloture := now();
  ELSIF NEW.statut <> 'resolu' THEN
    NEW.date_cloture := NULL;
  END IF;
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_ticket_cloture
BEFORE UPDATE ON ticket
FOR EACH ROW
EXECUTE FUNCTION f_ticket_cloture();

CREATE OR ALTER TRIGGER dbo.trg_poste_affectation
ON dbo.poste
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.historique_affectation (id_poste, ancien_employe, nouvel_employe, modifie_par)
  SELECT i.id_poste, d.id_employe, i.id_employe, SUSER_SNAME()
  FROM inserted i
  JOIN deleted d ON d.id_poste = i.id_poste
  WHERE i.id_employe <> d.id_employe
     OR (i.id_employe IS NULL AND d.id_employe IS NOT NULL)
     OR (i.id_employe IS NOT NULL AND d.id_employe IS NULL);
END;
GO

CREATE OR ALTER PROCEDURE dbo.reaffecter_poste @id_poste INT, @id_employe INT
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.poste SET id_employe = @id_employe WHERE id_poste = @id_poste;
    IF @@ROWCOUNT = 0
      THROW 50010, N'Poste inconnu', 1;
    INSERT INTO dbo.intervention (id_ticket, id_technicien, date_intervention, duree_min)
    VALUES (7, 11, SYSDATETIME(), 20);
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
  END CATCH;
END;