| Documentation PostgreSQL 8.0.25 | ||||
|---|---|---|---|---|
| Précédent | Arrière rapide | Avance rapide | Suivant | |
CREATE [ OR REPLACE ] FUNCTION nom
( [ [ 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 [, ...] ) ]CREATE FUNCTION définit une nouvelle fonction. CREATE OR REPLACE FUNCTION créera une nouvelle fonction ou remplacera une fonction existante.
Si un nom de schéma est inclus, alors 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 à une autre fonction existante avec les mêmes types d'argument dans le même schéma. Néanmoins, les fonctions de types d'arguments différents pourraient partager un nom (ceci est appelé le surchargement).
Pour mettre à jour la définition d'une fonction existante, utilisez CREATE OR REPLACE FUNCTION. Il n'est pas possible de changer le nom ou les types d'argument d'une fonction de cette façon (si vous avez essayé, vous devrez seulement créer une nouvelle fonction distincte). De même, CREATE OR REPLACE FUNCTION ne vous laissera pas modifier le type en retour d'une fonction existante. Pour cela, vous devez supprimer et recréer la fonction.
Si vous supprimez, puis recréez une fonction, la nouvelle fonction n'est pas la même entité que l'ancienne ; vous devrez supprimer les règles, vues, déclencheurs, etc. qui référençaient l'ancienne fonction. Utilisez CREATE OR REPLACE FUNCTION pour modifier la définition d'une fonction sans casser d'objets qui se réfèrent à la fonction.
L'utilisateur qui crée la fonction devient le propriétaire de la fonction.
Le nom de la fonction à créer (pouvant être qualifié du nom du schéma).
Le nom d'un argument. Quelques langages (actuellement seulement PL/pgSQL) vous laissent utiliser le nom dans le corps de la fonction. Pour les autres langages, le nom de l'argument est une documentation supplémentaire.
Le(s) type(s) de données des arguments de la fonction (pouvant être qualifié par le nom du schéma), s'il y en a. Les types des arguments peuvent être de base, composite ou de domaines, ou pourraient aussi référencer le type d'une colonne.
Suivant le langage d'implémentation, il pourrait aussi être autorisé de spécifier des << pseudotypes >> plutôt 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 SQL.
Le type d'une colonne est utilisé en écrivant nomtable.nomcolonne%TYPE. Utiliser cette fonctionnalité peut quelque fois aider à rendre une fonction dépendante des modifications de la définition d'une table.
Le type de données en retour (pouvant être qualifié du nom du schéma). Le type de retour pourrait être un type de base, complexe ou un domaine, ou pourrait être spécifié pour référencer le type d'une colonne existante. Suivant le langage d'implémentation, il pourrait aussi être autorisé de spécifier un << pseudotype >> tel que cstring.
Le modificateur SETOF indique que la fonction renverra un ensemble d'éléments plutôt qu'un seul élément.
Le type d'une colonne est référencé en écrivant nomtable.nomcolonne%TYPE.
Le nom du langage dans laquelle la fonction est implémentée. Pourrait être SQL, C, internal ou le nom d'un langage de procédures défini par l'utilisateur. Pour une compatibilité descendante, le nom peut être englobé avec des guillemets simples.
Ces attributs informent le système s'il est sain de remplacer plusieurs évaluations de la fonction avec une seule évaluation pour une optimisation en exécution. Au plus un choix devra être donné. Si aucun n'apparaît, VOLATILE est la valeur par défaut.
IMMUTABLE indique que la fonction renvoie toujours le même résultat si elle reçoit les mêmes valeurs en argument ; c'est-à-dire qu'elle n'effectue pas de recherches dans la base de données ou, autrement, qu'elle utilise l'information non présente directement dans la liste d'arguments. Si cette option est donné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, la fonction renverra le même résultat pour les mêmes valeurs
d'argument, mais son résultat pourrait varier au travers des
instructions SQL. Ceci est la sélection appropriée pour les fonctions
dont les résultats dépendent des recherches en base de données, des
variables paramètres (tel que la zone horaire en cours), etc. Notez aussi
que la famille de fonctions current_timestamp est qualifiée
de stable car leur valeur ne change pas à l'intérieur d'une transaction.
VOLATILE indique que la valeur de la fonction peut changer même avec un seul parcours de table, donc aucune optimisation ne peut être réalisée. Relativement peu de fonctions de bases de données sont volatiles dans ce sens ; quelques exemples sont random(), currval(), timeofday(). Notez que toute fonction qui a des effets de bord doit être classée comme volatile, même si son résultat est assez prévisible pour empêcher l'optimisation des appels ; un exemple est setval().
Pour des détails supplémenaires, voir Section 31.6.
CALLED ON NULL INPUT (la valeur par défaut) indique que la fonction sera appelée normalement quand certains de ses arguments sont NULL. C'est alors de la responsabilité de l'auteur de la fonction de vérifier les valeurs NULL si nécessaire et de répondre en conséquence.
RETURNS NULL ON NULL INPUT ou STRICT indiquent que la fonction renvoie toujours NULL si un de ces arguments est NULL. Si ce paramètre est spécifié, la fonction n'est pas exécutée quand il y a des arguments NULL ; à la place, un résultat NULL est automatiquement renvoyé.
SECURITY INVOKER indique que la fonction doit être 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 doit être exécutée avec les droits de l'utilisateur qui l'a créé.
Le mot clé EXTERNAL est présent pour la conformité SQL mais est optionnelle car, contrairement à SQL, cette fonctionnalité ne s'applique qu'aux fonctions externes.
Une constante de type chaîne définissant la fonction ; la signification dépend du langage. Cela pourrait être un nom de fonction interne, le chemin vers un fichier objet, une commande SQL ou un texte dans un langage de procédures.
Cette forme de clause AS est utilisée pour les fonctions en langage C chargeables dynamiquement quand le nom de la fonction dans le code source C n'est pas le même que celui de la fonction C. 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 en cours de définition.
La façon historique de spécifier les morceaux optionnels d'informations sur la fonction. Les attributs suivants pourraient apparaître ici :
Équivalent à STRICT ou RETURNS NULL ON NULL INPUT
isCachable est un équivalent obsolète de IMMUTABLE ; il est toujours accepté pour des raisons de compatibilité ascendante.
Les noms d'attribut ne sont pas sensibles à la casse.
Référez-vous à Section 31.3 pour plus d'informations sur l'écriture de fonctions.
La syntaxe complète de type SQL est autorisée pour les arguments en entrée et pour la valeur de sortie. Néanmoins, quelques détails de spécification de type (c'est-à-dire le champ précision pour le type numeric) sont de la responsabilité de l'implémentation de la fonction sous-jacente et sont silencieusement avalés (c'est-à-dire non reconnus ou forcés) par la commande CREATE FUNCTION.
PostgreSQL autorise le surchargement de fonctions ; c'est-à-dire que le même nom peut être utilisé pour plusieurs fonctions différentes si tant est qu'elles ont des types d'arguments distincts. Néanmoins, les noms C de toutes les fonctions doivent être différents, donc vous devez donner des noms différentes aux fonctions C suchargées (par exemple, utilisez les types d'argument comme morceaux des noms des fonctions C).
Lors d'appels répétés à CREATE FUNCTION et se référant au même fichier objet, le fichier est chargé une seule fois. Pour décharger et recharger le fichier (peut-être pendant le développement), utilisez la commande LOAD.
Utilisez DROP FUNCTION pour supprimer les fonctions définies par l'utilisateur.
Il est souvent utile d'utiliser les guillemets dollar (voir Section 4.1.2.2) pour écrire la chaîne de définition d'une fonction, plutôt que la syntaxe habituelle à guillemets simples. Sans guillemets dollar, tout guillemet simple et tout antislash devrait être échappé en les doublant dans la définition de la fonction.
Pour être capable de définir une fonction, l'utilisateur doit avoir le droit USAGE sur le langage.
Voici un exemple trivial pour vous aider à commencer. Pour plus d'informations et d'exemples, voir Section 31.3.
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
Incrémente 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;
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;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.
| Précédent | Sommaire | Suivant |
| CREATE DOMAIN | Niveau supérieur | CREATE GROUP |