CREATE [ OR REPLACE ] FUNCTION
    nom ( [ [ modearg ] [ nomarg ] typearg  [ { DEFAULT | = } defexpr ] [, ...] ] ) ] )
    [ RETURNS type_ret
      | RETURNS TABLE ( nom_colonne type_colonne [, ...] ) ]
  { LANGUAGE nomlang
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
    | COST cout_execution
    | ROWS nb_lignes_resultat
    | SET parametre { TO value | = value | FROM CURRENT }
    | 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 indiqué. Sinon, elle est créée dans le schéma courant. Le nom de la nouvelle fonction ne peut pas correspondre à celui d'une fonction existant avec les mêmes types d'arguments en entrée dans le même schéma. Toutefois, les fonctions de types d'arguments différents peuvent partager le même nom (ceci est appelé surcharge).

Pour remplacer la définition actuelle 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. De plus, ALTER FUNCTION peut être utilisé pour modifier la plupart des propriétés supplémentaires d'une fonction existante.

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

Paramètres

Notes

La lecture de Section 34.3, « Fonctions 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 la surcharge des fonctions ; c'est-à-dire que le même nom peut être utilisé pour des fonctions différentes si tant est qu'elles aient des types d'arguments en entrée distincts. Néanmoins, les noms C de toutes les fonctions doivent être différents. Il est donc nécessaire de donner des noms différents 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 truc(int) ...
CREATE FUNCTION truc(int, out text) ...

Des fonctions ayant des listes de types d'arguments différents ne seront pas considérées comme en conflit au moment de leur création, mais si des valeurs par défauts sont fournies, elles peuvent se retrouver en conflit au moment de l'invocation. Considérez par exemple :

CREATE FUNCTION truc(int) ...
CREATE FUNCTION truc(int, int default 42) ...

Un appel truc(10) échouera à cause de l'ambiguïté sur la fonction à appeler.

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 par session. Pour décharger et recharger le fichier (en phase de développement, par exemple), démarrez une nouvelle session.

DROP FUNCTION est utilisé pour supprimer les fonctions utilisateur.

Les guillemets dollar (voir Section 4.1.2.4, « 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.

Si une SET est attachée à une fonction, alors les effets d'une commande SET LOCAL exécutée dans la fonction pour la même variable seront restreints à la fonction : la valeur précedente du paramètre sera toujours restaurée à la sortie de la fonction. Néanmoins, une commande SET standard (sans LOCAL) surcharge la clause SET, comme il le ferait avec une SET LOCAL précédente : les effets d'une telle commande subisteront à la sortie de la fonction sauf si la transaction en cours est annulée.

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).

Si une fonction est déclarée STRICT avec un argument VARIADIC, la vérification du côté strict d'une fonction teste si le tableau variadic lui-même est non NULL. La fonction sera quand même appelée si le tableau contient des éléments NULL.

Exemples

Quelques exemples triviaux pour bien débuter sont présentés ci-après. Pour plus d'informations et d'exemples, voir Section 34.3, « Fonctions 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);

Une autre façon de renvoyer plusieurs colonnes est d'utiliser une fonction TABLE :

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

SELECT * FROM dup(42);

Toutefois, une fonction TABLE est différente des exemples précédents parce qu'elle retourne en fait un ensemble d'enregistrements, pas juste un enregistrement.

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

Parce qu'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 les schémas modifiables par des utilisateurs indignes de confiance. Cela empêche des utilisateurs malveillants de créer des objets qui masquent les objets utilisés par la fonction. Dans ce sens, le schéma des tables temporaires est particulièrement important car il est le premier schéma parcouru et qu'il est normalement modifiable par tous les utilisateurs. Une solution consiste à forcer le parcours de ce schéma en dernier lieu. Pour cela, on écrit pg_temp comme 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;
BEGIN
        -- Effectuer le travail sécurisé de la fonction.
        SELECT  (motdepasse = $2) INTO ok
        FROM    motsdepasse
        WHERE   nomutilisateur = $1;

        RETURN ok;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Configure un search_path sécurisée : les schémas de confiance, puis 'pg_temp'.
    SET search_path = admin, pg_temp;

Avant PostgreSQL™ 8.3, l'option SET n'était pas disponible, donc les anciennes fonctions pouvaient contenir un code assez complexe pour sauvegarder, initialiser puis restaurer un paramètre comme search_path. L'option SET est plus simple à utiliser dans ce but.

Un autre point à garder en mémoire est que, par défaut, le droit d'exécution est donné à PUBLIC pour les fonctions nouvellement créées (voir GRANT pour plus d'informations). Fréquemment, vous souhaiterez restreindre l'utilisation d'une fonction « security definer » à seulement quelques utilisateurs. Pour cela, vous devez révoquer les droits PUBLIC puis donner le droit d'exécution aux utilisateurs sélectionnés. Pour éviter que la nouvelle fonction soit accessible à tous pendant un court moment, créez-la et initialisez les droits dans une même transaction. Par exemple :

BEGIN;
CREATE FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION verifie_motdepasse(unom TEXT, motpasse TEXT) TO admins;
COMMIT;

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.

Le standard SQL ne définit pas de paramètres par défaut. La syntaxe avec le mot clé DEFAULT provient d'Oracle, et elle est assez proche de l'esprit du standard : SQL/PSQL l'utilise pour les valeurs par défaut de variables. La syntaxe avec = est utilisée dans T-SQL et Firebird.

Voir aussi

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE, createlang