PostgreSQLLa base de données la plus sophistiquée au monde.

CREATE FUNCTION

CREATE FUNCTION — Définir une nouvelle fonction

Synopsis

CREATE [ OR REPLACE ] FUNCTION
    nom ( [ [ modearg ] [ nomarg ] typearg [, ...] ] )
    [ RETURNS type_ret ]
  { LANGUAGE nomlang
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
    | AS 'definition'
    | AS 'fichier_obj', 'symbole_lien'
  } ...
    [ WITH ( attribut [, ...] ) ]

Description

CREATE FUNCTION définit une nouvelle fonction. CREATE OR REPLACE FUNCTION crée une nouvelle fonction ou la remplace si elle existe déjà.

Si un nom de schéma est précisé, la fonction est créée dans le schéma spécifié. Sinon, elle est créée dans le schéma courant. Le nom de la nouvelle fonction ne doit pas correspondre à celui d'une autre fonction existant avec les mêmes types d'arguments dans le même schéma. Néanmoins, les fonctions de types d'arguments différents peuvent partager le même nom (ceci est appelé surchargement).

Pour mettre à jour la définition d'une fonction existante, CREATE OR REPLACE FUNCTION est utilisé. Il n'est pas possible de changer le nom ou les types d'argument d'une fonction de cette façon (cela crée une nouvelle fonction distincte). De même, CREATE OR REPLACE FUNCTION ne permet pas de modifier le type retour d'une fonction existante. Pour cela, il est nécessaire de supprimer et de recréer la fonction. (Lors de l'utilisation de paramètres OUT, cela signifie que le nom ou le type d'un paramètre OUT ne peut être modifié que par la suppression de la fonction.)

En cas de suppression et de recréaction d'une fonction, la nouvelle fonction n'est pas la même entité que l'ancienne ; il faut supprimer les règles, vues, déclencheurs, etc. qui référencent l'ancienne fonction. CREATE OR REPLACE FUNCTION permet de modifier la définition d'une fonction sans casser les objets qui s'y réfèrent.

L'utilisateur qui crée la fonction en devient le propriétaire.

Paramètres

nom

Le nom de la fonction à créer (éventuellement qualifié du nom du schéma).

modearg

Le mode d'un argument : soit IN, soit OUT, soit INOUT. En cas d'omission, la valeur par défaut est IN.

nomarg

Le nom d'un argument. Quelques langages (seul PL/pgSQL, en réalité) permettent d'utiliser ce nom dans le corps de la fonction. Pour les autres langages, le nom d'un argument en entrée est purement documentaire. En revanche, le nom d'un argument en sortie a une utilité car il définit le nom de la colonne dans la ligne résultat. (En cas d'omission du nom d'un argument en sortie, le système choisit un nom de colonne par défaut.)

argtype

Le(s) type(s) de données des arguments de la fonction (éventuellement qualifié du nom du schéma), s'il y en a. Les types des arguments peuvent être basiques, composites ou de domaines, ou faire référence au type d'une colonne.

Suivant le langage d'implémentation, il est possible de spécifier des « pseudotypes » tels que des cstring. Les pseudotypes indiquent que le type d'argument réel est soit non complètement spécifié, soit en dehors de l'ensemble des types de données ordinaires du SQL.

Il est fait référence au type d'une colonne par nomtable.nomcolonne%TYPE. Cette fonctionnalité peut servir à rendre une fonction indépendante des modifications de la définition d'une table.

type_ret

Le type de données en retour (éventuellement qualifié du nom du schéma). Le type de retour peut être un type de base, composite ou de domaine, ou faire référence au type d'une colonne existante. En fonction du langage d'implémentation, il est possible de spécifier un « pseudotype » tel que cstring.

Quand il y a des paramètres OUT ou INOUT, la clause RETURNS peut être omise. Si elle est présente, elle doit correspondre au type de résultat imposé par les paramètres de sortie : RECORD s'il y en a plusieurs, ou le type du seul paramètre en sortie.

Le modificateur SETOF indique que la fonction retourne un ensemble d'éléments plutôt qu'un seul.

Il est fait référence au type d'une colonne par nomtable.nomcolonne%TYPE.

nomlang

Le nom du langage d'écriture de la fonction. Peut être SQL, C, internal ou le nom d'un langage procédural utilisateur. Pour des raisons de compatibilité descendante, le nom peut être écrit entre guillemets simples.

IMMUTABLE, STABLE, VOLATILE

Ces attributs agissent sur l'optimisation en exécution. Ils permettent de préciser si plusieurs évaluations de la fonction peuvent sans risque être remplacée par une seule. Un seul choix est possible. En son absence, VOLATILE est utilisé.

IMMUTABLE indique que, à arguments constants, la fonction renvoie toujours le même résultat ; c'est-à-dire qu'elle n'effectue pas de recherches dans la base de données, ou alors qu'elle utilise des informations non directement présentes dans la liste d'arguments. Si cette option est précisée, tout appel de la fonction avec des arguments constants peut être immédiatement remplacé par la valeur de la fonction.

STABLE indique qu'à l'intérieur d'un seul parcours de la table, à arguments constants, la fonction retourne le même résultat, mais celui-ci varie en fonction des instructions SQL. Cette option est appropriée pour les fonctions dont les résultats dépendent des recherches en base, des variables de paramètres (tel que la zone horaire courante), etc. La famille de fonctions current_timestamp est qualifiée de stable car les valeurs de ces fonctions ne changent pas à l'intérieur d'une transaction.

VOLATILE indique que la valeur de la fonction peut changer même au cours d'un seul parcours de table. Aucune optimisation ne peut donc être réalisée. Relativement peu de fonctions de bases de données sont volatiles dans ce sens ; quelques exemples sont random(), currval(), timeofday(). Toute fonction qui a des effets de bord doit être classée volatile, même si son résultat est assez prévisible. Cela afin d'éviter l'optimisation des appels ; setval() en est un exemple.

Pour des détails supplémenaires, voir Section 32.6, « Catégories de volatibilité des fonctions ».

CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT, STRICT

CALLED ON NULL INPUT (la valeur par défaut) indique que la fonction sera appelée normalement si certains de ses arguments sont NULL. C'est alors de la responsabilité de l'auteur de la fonction de gérer les valeurs NULL.

RETURNS NULL ON NULL INPUT ou STRICT indiquent que la fonction renvoie toujours NULL si l'un de ses arguments est NULL. Si un de ces paramètres est spécifié, et qu'un des arguments est NULL, la fonction n'est pas exécutée, mais un résultat NULL est automatiquement retournée.

[EXTERNAL] SECURITY INVOKER, [EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indique que la fonction est exécutée avec les droits de l'utilisateur qui l'appelle. C'est la valeur par défaut. SECURITY DEFINER spécifie que la fonction est exécutée avec les droits de l'utilisateur qui l'a créé.

Le mot clé EXTERNAL est autorisé pour la conformité SQL mais il est optionnel car, contrairement à SQL, cette fonctionnalité s'applique à toutes les fonctions, pas seulement celles externes.

definition

Une constante de type chaîne définissant la fonction ; la signification dépend du langage. Cela peut être un nom de fonction interne, le chemin vers un fichier objet, une commande SQL ou du texte en langage procédural.

fichier_obj, symbole_lien

Cette forme de clause AS est utilisée pour les fonctions en langage C chargeables dynamiquement lorsque le nom de la fonction dans le code source C n'est pas le même que celui de la fonction SQL. La chaîne fichier_obj est le nom du fichier contenant l'objet chargeable dynamiquement et symbole_lien est le symbole de lien de la fonction, c'est-à-dire le nom de la fonction dans le code source C. Si ce lien est omis, il est supposé être le même que le nom de la fonction SQL définie.

attribut

Façon historique de spécifier des informations optionnelles concernant la fonction. Les attributs suivants peuvent apparaître ici :

isStrict

Équivalent à STRICT ou RETURNS NULL ON NULL INPUT

isCachable

isCachable est un équivalent obsolète de IMMUTABLE ; il est toujours accepté pour des raisons de compatibilité ascendante.

Les noms d'attribut sont insensibles à la casse.

Notes

La lecture de Section 32.3, « Fonctions définies par l'utilisateur » fournit des informations supplémentaires sur l'écriture de fonctions.

Toute la syntaxe des types SQL est autorisée pour les arguments en entrée et la valeur de sortie. Néanmoins, quelques détails de spécification de type (le champ précision pour le type numeric, par exemple) sont de la responsabilité de l'implantation de la fonction sous-jacente et sont silencieusement acceptés (c'est-à-dire non reconnus ou vérifiés) par la commande CREATE FUNCTION.

PostgreSQL™ autorise le surchargement des fonctions ; c'est-à-dire que le même nom peut être utilisé pour dess fonctions différentes si tant est qu'elles aient des types d'arguments distincts. Néanmoins, les noms C de toutes les fonctions doivent être différents. Il est donc nécessaire de donner des noms différentes aux fonctions C suchargées (on peut, par exemple, utiliser le type des arguments dans le nom de la fonction).

Deux fonctions sont considérées identiques si elles partagent le même nom et les mêmes types d'argument en entrée, sans considération des paramètres OUT. Les déclarations suivantes sont, de fait, en conflit :

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

Lors d'appels répétés à CREATE FUNCTION faisant référence au même fichier objet, le fichier est chargé une seule fois. Pour décharger et recharger le fichier (en phase de développement, par exemple), la commande LOAD est utilisée.

DROP FUNCTION est utilisé pour supprimer les fonctions utilisateur.

Les guillemets dollar (voir Section 4.1.2.2, « Constantes de chaînes avec guillemet dollar ») peuvent s'avérer plus utiles que la syntaxe habituelle à guillemets simples pour écrire la chaîne de définition d'une fonction. En l'absence de guillemets dollar, tout guillemet simple et tout antislash dans la définition de la fonction doit être échappé en le doublant.

Pour pouvoir définir une fonction, l'utilisateur doit posséder le droit USAGE sur le langage.

Lorsque CREATE OR REPLACE FUNCTION est utilisé pour remplacer une fonction existante, le propriétaire et les droits de la fonction ne sont pas changés. Toutes les autres propriétés de la fonction sont affectées par les valeurs indiquées ou par les valeurs imploquées par la commande. Vous devez être le propriétaire de la fonction pour la remplacer (cela inclut aussi les membres du rôle propriétaire).

Exemples

Voici quelques exemples triviaux pour bien débuter. Pour plus d'informations et d'exemples, voir Section 32.3, « Fonctions définies par l'utilisateur ».

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Incrémenter un entier, en utilisant le nom de l'argument, dans PL/pgSQL :

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Renvoyer un enregistrement contenant plusieurs paramètres en sortie :

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

La même chose, en plus verbeux, avec un type composite nommé explicitement :

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

Écrire des fonctions SECURITY DEFINER en toute sécurité

Comme une fonction SECURITY DEFINER est exécutée avec les droits de l'utilisateur qui l'a créé, une certaine attention est nécessaire pour s'assurer que la fonction ne peut pas être utilisée de façon maline. Pour des raisons de sécurité, search_path doit être configuré pour exclure tout schéma où des utilisateurs qui ne sont pas de confiance pourraient écrire. Ceci empêche des utilisateurs malveillants de créer des objets qui masqueraient des objets utilisés par la fonction. Dans cette idée, le schéma des tables temporaires est particulièrement important car il est le premier schéma dans lequel a lieu la recherche et il est modifiable par tout utilisateur. Une solution est de forcer la recherche à ne prendre en condition ce schéma qu'en dernier lieu. Pour cela, écrire pg_temp en tant que dernière entrée de search_path. La fonction suivante illustre une utilisation sûre :

CREATE FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT)
RETURNS BOOLEAN AS $$
DECLARE ok BOOLEAN;
        ancien_path TEXT;
BEGIN
        -- Sauvegarder l'ancien search_path ;
        -- remarquez que nous devons qualifier current_setting
        -- pour nous assurer que nous appelons la bonne fonction
        ancien_path := pg_catalog.current_setting('search_path');

        -- Configurer un search_path sécurisé : schémas de confiance, puis 'pg_temp'.
        -- Nous initialisons is_local = true pour que l'ancienne valeur
        -- soit restaurée au cas où une erreur survienne avant que nous
        -- n'atteignons la fin de la fonction.
        PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true);

        -- Effectuer le travail sécurisé de la fonction.
        SELECT  (motdepasse = $2) INTO ok
        FROM    motsdepasse
        WHERE   nomutilisateur = $1;

        -- Restaurer le search_path de l'appelant
        PERFORM pg_catalog.set_config('search_path', ancien_path, true);

        RETURN ok;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Compatibilité

Une commande CREATE FUNCTION est définie en SQL:1999 et ultérieur. La version PostgreSQL™ est similaire mais pas entièrement compatible. Les attributs ne sont pas portables, pas plus que les différents langages disponibles.

Pour des raisons de compatibilité avec d'autres systèmes de bases de données, modearg peut être écrit avant ou après nomarg. Mais seule la première façon est compatible avec le standard.