PL/pgSQL & langages PL : introduction

Module PL0

Dalibo SCOP

25.09

5 septembre 2025

Sur ce document

Formation Module PL0
Titre PL/pgSQL & langages PL : introduction
Révision 25.09
PDF https://dali.bo/pl0_pdf
EPUB https://dali.bo/pl0_epub
HTML https://dali.bo/pl0_html
Slides https://dali.bo/pl0_slides

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

PL/pgSQL & langages PL : introduction

Introduction

Il n’y a pas que le SQL.

Au menu

  • Langages PL : PL/pgSQL & les autres
  • Exemples
  • Intérêt

Objectifs

  • Savoir ce qu’est un langage PL
  • Savoir quand les utiliser
  • Savoir coder une fonction simple

Langages PL : introduction

Qu’est-ce qu’un langage PL ?

  • PostgreSQL accepte des fonctions et procédures en SQL et C
    • utilisables depuis une requête SQL
  • PL = Procedural Language

Quels langages PL sont disponibles ?

  • Installé par défaut :
    • PL/pgSQL
  • Intégrés au projet :
    • PL/Perl
    • PL/Python
    • PL/Tcl
  • Extensions tierces :
    • PL/java, PL/R, PL/v8 (JavaScript), PL/R, PL/sh…
    • extensible à volonté

Langages trusted vs untrusted

  • Trusted = langage de confiance :
    • ne permet que l’accès à la base de données
    • pas aux fichiers, réseau, etc.
    • SQL, PL/pgSQL, PL/Perl, PL/Tcl
  • Untrusted:
    • PL/Python, C, PL/TclU, PL/PerlU
    • potentiellement dangereux !

Les langages PL de PostgreSQL

Les langages PL fournissent :

  • des fonctionnalités procédurales dans un univers relationnel
  • les fonctionnalités avancées du langage PL choisi
  • les librairies associées
  • des performances supérieures au code côté client
    • latence
    • indexation

Intérêts de PL/pgSQL en particulier

  • Dédié au traitement des données et au SQL
  • Maniement direct des données
  • Ajout de structures de contrôle au langage SQL
  • Traitements complexes, transactions
  • Hérite de tous les types, fonctions et opérateurs définis par les utilisateurs
  • Trusted
  • Facile à utiliser
  • Mais assez pauvre

Routines / Procédures stockées / Fonctions

  • Procédure stockée
    • pas de retour
    • contrôle transactionnel : COMMIT / ROLLBACK
  • Fonction
    • peut renvoyer des données (même des lignes)
    • utilisable dans un SELECT
    • peut être de type TRIGGER, agrégat, fenêtrage
  • Routine
    • procédure ou fonction

Exemples de fonction SQL

Fonction SQL renvoyant un calcul

Déclaration :

CREATE FUNCTION employe_eligible_prime_sql (service int, date_embauche date)
RETURNS boolean
LANGUAGE sql
AS $$
  SELECT ( service !=3 AND date_embauche < '2003-01-01')  ;
$$ ;

Utilisation :

SELECT matricule, num_service, nom, prenom
FROM    employes_big
WHERE   employe_eligible_prime_sql (num_service, date_embauche) = true ;

Fonction SQL renvoyant plusieurs champs

CREATE OR REPLACE FUNCTION explose_date_table (d date)
RETURNS TABLE (jour integer, mois integer, annee integer)
LANGUAGE sql
AS $$
  SELECT  extract (day FROM d)::int,
                   extract(month FROM d)::int,
                   extract (year FROM d)::int ;
$$ ;
SELECT * FROM explose_date ('31-12-2020');
 jour | mois | annee
------+------+-------
   31 |    0 |  2020

Fonction SQL renvoyant plusieurs lignes d’une table

CREATE OR REPLACE FUNCTION  tables_jamais_analyzees ()
RETURNS SETOF pg_stat_user_tables
LANGUAGE sql
AS $$
  SELECT * FROM pg_stat_user_tables
  WHERE coalesce(last_analyze, last_autoanalyze) IS NULL ;
$$ ;

Exemples de routines PL/pgSQL

Exemple de procédure PL/pgSQL avec gestion transactionnelle

CREATE OR REPLACE PROCEDURE vide_tables (dry_run BOOLEAN) AS $$
BEGIN
    TRUNCATE TABLE pgbench_history ;
    TRUNCATE TABLE pgbench_accounts CASCADE ;
    TRUNCATE TABLE pgbench_tellers  CASCADE ;
    TRUNCATE TABLE pgbench_branches CASCADE ;
    IF dry_run THEN
        ROLLBACK ;
    END IF ;
EXCEPTION WHEN undefined_table THEN
    RAISE NOTICE 'Table inexistante [%] %', SQLSTATE, SQLERRM;
    ROLLBACK ;
END ; $$ LANGUAGE plpgsql ;
CALL vide_tables (dry_run=>true);

Exemple de bloc anonyme avec variable, boucle sur une table, et SQL dynamique

DO $$
DECLARE r record ;
        n int := 0 ;
BEGIN
    FOR r IN (SELECT schemaname, relname
              FROM pg_stat_user_tables
              WHERE coalesce(last_analyze, last_autoanalyze) IS NULL
              ) LOOP
        n := n+1 ;
        RAISE NOTICE '% - Analyze de : %.%', n, r.schemaname, r.relname ;
        EXECUTE 'ANALYZE ' || quote_ident(r.schemaname)
                           || '.' || quote_ident(r.relname) ;
    END LOOP;
END $$ ;

Exemple de trigger

CREATE OR REPLACE FUNCTION horodatage() RETURNS trigger
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.date_ajout := now();
  ELSEIF TG_OP = 'UPDATE' THEN
    NEW.date_modif := now();
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trig_horodatage
   BEFORE INSERT OR UPDATE ON ma_table
   FOR EACH ROW
   EXECUTE PROCEDURE horodatage();

Conclusion

Questions

N’hésitez pas, c’est le moment !

Quiz

https://dali.bo/pl0_quiz