Menu

PostgreSQL CREATE FUNCTION et procédures stockées

CREATE FUNCTION définit une fonction que vous appelez dans vos requêtes, écrite en SQL ou en PL/pgSQL. CREATE PROCEDURE définit une routine que vous lancez avec CALL et qui peut valider des transactions en cours de route.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

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.

FonctionProcédure
Se lance avecSELECT f(...), n'importe où dans une requêteCALL p(...) uniquement
Renvoieune valeur, une ligne ou un ensemble de lignesrien (ou des paramètres OUT/INOUT)
COMMIT / ROLLBACK dans le corpsnonoui, en PL/pgSQL, hors d'un bloc de transaction
DepuistoujoursPostgreSQL 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 utilisent random() 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);.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer