CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; définit une fonction que les requêtes peuvent appeler comme une fonction intégrée. Le corps se place entre deux marques $$, en SQL simple ou en PL/pgSQL, le langage procédural de PostgreSQL. CREATE PROCEDURE (PostgreSQL 11 et plus) définit à la place une routine que vous lancez avec CALL.
with_tax(50) prend le taux par défaut et renvoie 60 ; with_tax(50, 0.1) renvoie 55. Les arguments peuvent aussi se passer par nom : with_tax(50, rate => 0.1).
Écrire de la logique en PL/pgSQL
Utilisez LANGUAGE plpgsql quand la fonction a besoin de variables, de IF, de boucles ou de gestion d'erreurs. Les variables vont dans une section DECLARE, et le code entre BEGIN et END :
Un colis de 4 kg coûte le tarif de base, 5 ; un colis express de 20 kg coûte (5 + 10 * 0.5) * 2 = 20. En PL/pgSQL, RAISE NOTICE 'value: %', cost; affiche un message et RAISE EXCEPTION interrompt l'exécution avec une erreur.
Quand le SQL simple suffit à exprimer la logique, préférez LANGUAGE sql : le planificateur peut intégrer une fonction SQL simple directement dans la requête qui l'appelle.
Renvoyer un ensemble de lignes
RETURNS TABLE (...) crée une fonction qui renvoie des lignes. Appelez-la dans FROM comme une table :
orders_of('ana') renvoie les commandes 1 et 3. RETURNS SETOF orders renvoie des lignes entières de la table orders au lieu des colonnes listées.
Depuis PostgreSQL 14, une fonction SQL peut avoir un corps en SQL standard au lieu d'une chaîne. PostgreSQL le stocke déjà analysé et note les tables et colonnes qu'il utilise, qui ne peuvent donc pas être supprimées tant que la fonction existe :
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Créer une procédure stockée et la lancer avec CALL
Une procédure n'a pas de type de retour ; vous la lancez avec CALL. Elle peut modifier des données et, quand le CALL n'est pas dans un bloc de transaction explicite, faire un COMMIT en cours de route. C'est ainsi qu'on traite un gros travail par lots.
Le compte 1 finit à 70 et le compte 2 à 80. Un virement de 200 violerait la contrainte CHECK du compte 1, et l'erreur annulerait les deux mises à jour.
| Fonction | Procédure | |
|---|---|---|
| Se lance avec | SELECT f(...), n'importe où dans une requête | CALL p(...) uniquement |
| Renvoie | une valeur, une ligne ou un ensemble de lignes | rien (ou des paramètres OUT/INOUT) |
COMMIT / ROLLBACK dans le corps | non | oui, en PL/pgSQL, hors d'un bloc de transaction |
| Depuis | toujours | PostgreSQL 11 |
Indiquer la volatilité : IMMUTABLE, STABLE, VOLATILE
L'étiquette de volatilité dit au planificateur ce qu'il peut supposer d'une fonction. La valeur par défaut est VOLATILE.
IMMUTABLE: le résultat ne dépend que des arguments et ne change jamais (with_tax). Obligatoire pour une fonction utilisée dans une expression d'index.STABLE: le résultat peut changer d'une instruction à l'autre mais pas au sein d'une même instruction, par exemple une fonction qui lit des tables (orders_of).VOLATILE: tout le reste, comme les fonctions qui utilisentrandom()ou modifient des données. PostgreSQL l'appelle de nouveau pour chaque ligne.
Une mauvaise étiquette donne de mauvais résultats : une fonction IMMUTABLE qui lit une table peut renvoyer des valeurs périmées depuis un index ou un plan en cache.
Remplacer, surcharger et supprimer une fonction
CREATE OR REPLACE FUNCTION change le corps d'une fonction existante mais pas son type de retour ; pour cela, supprimez-la d'abord. PostgreSQL identifie une fonction par son nom et les types de ses arguments, donc f(int) et f(text) sont deux fonctions. Pour supprimer l'une de deux fonctions de même nom, donnez les types des arguments :
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
Le chapitre PL/pgSQL documente les boucles, les curseurs, les exceptions et le SQL dynamique avec EXECUTE. Pour lancer une fonction automatiquement quand des lignes changent, attachez-la à un trigger.
Questions fréquentes
Quelle est la différence entre une fonction et une procédure en PostgreSQL ?
Une fonction renvoie une valeur et s'appelle dans une requête (SELECT f(x)). Une procédure, arrivée avec PostgreSQL 11, se lance seule avec CALL et peut faire COMMIT ou ROLLBACK dans son corps ; une fonction tourne toujours dans la transaction de l'appelant.
Que signifie $$ dans une fonction PostgreSQL ?
$$ est le dollar quoting : il marque le début et la fin d'une chaîne littérale, donc le corps de la fonction peut contenir des apostrophes sans les échapper. Vous pouvez aussi ajouter une étiquette, comme dans $body$ ... $body$.
Comment voir le code d'une fonction PostgreSQL ?
Dans psql, \df liste les fonctions et \sf name affiche la définition d'une fonction. En SQL, SELECT pg_get_functiondef('name'::regproc); renvoie l'instruction CREATE OR REPLACE FUNCTION complète.
Une fonction PostgreSQL peut-elle renvoyer plusieurs lignes ?
Oui. Déclarez-la RETURNS TABLE (col type, ...) ou RETURNS SETOF type et appelez-la dans la clause FROM comme une table : SELECT * FROM f(1);.