CREATE FUNCTION — Définir une nouvelle fonction
CREATE [ OR REPLACE ] FUNCTION
nom ( [ [ modearg ] [ nomarg ] typearg [ { DEFAULT | = } expression_par_defaut ] [, ...] ] ) ] )
[ RETURNS type_ret
| RETURNS TABLE ( nom_colonne type_colonne [, ...] ) ]
{ LANGUAGE nom_lang
| TRANSFORM { FOR TYPE nom_type } [, ... ]
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST cout_execution
| ROWS nb_lignes_resultat
| SET parametre { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'fichier_obj', 'symbole_lien'
} ...
[ WITH ( attribut [, ...] ) ]
CREATE FUNCTION définit une nouvelle fonction. CREATE OR REPLACE FUNCTION crée une nouvelle fonction ou la remplace si elle existe déjà. Pour pouvoir créer une fonction, l'utilisateur doit avoir le droit USAGE sur le langage associé.
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 type d'un paramètre OUT ne peut être modifié que par la suppression de la fonction.)
Quand CREATE OR REPLACE FUNCTION est utilisé pour remplacer une fonction existante, le propriétaire et les droits de la fonction ne changent pas. Toutes les autres propriétés de la fonction se voient affectées les valeurs spécifiées dans la commande ou implicites pour les autres. Vous devez être le propriétaire de la fonction pour la remplacer ou être un membre du rôle propriétaire 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.
Pour pouvoir créer une fonction, vous devez avoir le droit USAGE sur les types des arguments et de la valeur de retour.
Le nom de la fonction à créer (éventuellement qualifié du nom du schéma).
Le mode d'un argument : IN, OUT, INOUT ou VARIADIC. En cas d'omission, la valeur par défaut est IN. Seuls des arguments OUT peuvent suivre un argument VARIADIC. Par ailleurs, des arguments OUT et INOUT ne peuvent pas être utilisés en même temps que la notation RETURNS TABLE.
Le nom d'un argument. Quelques langages (incluant SQL et PL/pgSQL) 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 ce qui concerne la fonction elle-même. Mais vous pouvez utiliser les noms d'arguments en entrée lors de l'appel d'une fonction pour améliorer la lisibilité (voir Section 4.3, « Fonctions appelantes »). Dans tous les cas, 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.)
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.
En fonction du langage, il est possible d'indiquer des « pseudotypes », tel que 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 nom_table.nomcolonne%TYPE. Cette fonctionnalité peut servir à rendre une fonction indépendante des modifications de la définition d'une table.
Une expression à utiliser en tant que valeur par défaut si le paramètre n'est pas spécifié. L'expression doit pouvoir être coercible dans le type d'argument du paramètre. Seuls les paramètres d'entrée (dont les INOUT) peuvent avoir une valeur par défaut. Tous les paramètres d'entrée suivant un paramètre avec une valeur par défaut doivent aussi avoir une valeur par défaut.
Le type de données en retour (éventuellement qualifié du nom du schéma). Le type de retour peut être un type basique, composite ou de domaine, ou faire référence au type d'une colonne existante. En fonction du langage, il est possible d'indiquer un « pseudotype », tel que cstring. Si la fonction ne doit pas renvoyer de valeur, on indique void comme type de retour.
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 nom_table.nomcolonne%TYPE.
Le nom d'une colonne de sortie dans la syntaxe RETURNS TABLE. C'est une autre façon de déclarer un paramètre OUT nommé, à la différence près que RETURNS TABLE implique aussi RETURNS SETOF.
Le type de données d'une colonne de sortie dans la syntaxe RETURNS TABLE.
Le nom du langage d'écriture de la fonction. Peut être SQL, C, internal ou le nom d'un langage procédural utilisateur, e.g. plpgsql. Entourer le nom de guillemets simples est une pratique obsolète et nécessite la bonne casse.
Indique la transformation s'appliquant pour un appel à la fonction. Les transformations convertissent des types de données SQL en des types de données spécifiques au langage. Voir CREATE TRANSFORM(7). Les implémentations des langages de procédure stockée ont une connaissance codée en dur des types internes, donc ces derniers n'ont pas besoin d'être listés ici. Si l'implémentation d'un langage de procédure ne sait pas gérer un type et qu'aucune transformation n'est fournie, il y a un retour au comportement par défaut pour les conversions des types de données mais ceci dépends de l'implémentation.
WINDOW indique que la fonction est une fonction window plutôt qu'une fonction simple. Ceci n'est à l'heure actuelle utilisable que pour les fonctions écrites en C. L'attribut WINDOW ne peut pas être changé lors du remplacement d'une définition de fonction existante.
Ces attributs informent l'optimiseur de requêtes sur le comportement de la fonction. Un seul choix est possible. En son absence, VOLATILE est utilisé.
IMMUTABLE indique que la fonction ne peut pas modifier la base de données et qu'à 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 que la fonction ne peut pas modifier la base de données et 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. (Ce mode est inapproprié pour les triggers AFTER qui souhaitent voir les lignes modifiées par la commande en cours.) 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 complémentaires, voir Section 37.6, « Catégories de volatilité des fonctions ».
LEAKPROOF indique que la fonction n'a pas d'effets de bord. Elle ne fournit aucune information sur ces arguments autrement que par sa valeur de retour. Par exemple, une fonction qui renvoie un message d'erreur pour certaines valeurs d'arguments et pas pour d'autres, ou qui inclut les valeurs des arguments dans des messages d'erreur, ne peut pas utiliser cette clause. Ceci affecte la façon dont le système exécute des requêtes contre les vues créées avec l'option security_barrier ou les tables avec la fonctionnalité RLS activée. Le système force les conditions des politiques de sécurité et les vues avec barrière de sécurité avant toute condition fournie par l'utilisateur sur la requête appelante qui contient des fonctions non sécurisées (non LEAKPROOF), pour empêcher toute exposition involontaire des données. Les fonctions et opérateurs marquées LEAKPROOF sont supposés être sûrs, et peuvent être exécutées avant les conditions des politiques de sécurité et les vues avec barrière de sécurité. De plus, les fonctions qui ne prennent pas d'arguments ou qui ne se voient pas fournies d'arguments par la vue ou la table n'ont pas besoin d'être marquées comme LEAKPROOF pour être exécutées avant les conditions de sécurité. Voir CREATE VIEW(7) et Section 40.5, « Règles et droits ». Cette option peut seulement être utilisée par un superutilisateur.
CALLED ON NULL INPUT (la valeur par défaut) indique que la fonction est 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. Lorsque ce paramètre est utilisé et qu'un des arguments est NULL, la fonction n'est pas exécutée, mais un résultat NULL est automatiquement retourné.
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 en est le propriétaire.
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.
PARALLEL UNSAFE indique que la fonction ne peut pas être exécutée dans le mode parallèle. La présence d'une fonction de ce type dans une requête SQL force un plan d'exécution en série. C'est la valeur par défaut. PARALLEL RESTRICTED indique que la fonction peut être exécutée en mode parallèle mais l'exécution est restreinte au processus principal d'exécution. PARALLEL SAFE indique que la fonction s'exécute correctement dans le mode parallèle sans restriction.
Les fonctions doivent être marquées comme non parallélisable si elles modifient l'état d'une base ou si elles font des changements sur la transaction telles que l'utilisation de sous-transactions ou si elles accèdent à des séquences ou tentent de faire des modifications persistentes aux configurations (par exemple setval). Elles doivent être marquées comme restreintes au parallélisme si elles accèdent aux tables temporaires, à l'état de connexion des clients, aux curseurs, aux requêtes préparées ou à un état local du moteur où le système ne peut pas synchroniser en mode parallèle (par exemple, setseed ne peut pas être exécuté autrement que par le processus principal car une modification réalisée par un autre processus ne pourrait pas être reflété dans le processus principal). En général, si une fonction est marquée sûre à la parallélisation alors qu'elle est restreinte ou non parallélisable ou si elle est marquée restreinte quand elle est en fait non parallélisable, elle pourrait renvoyer des erreurs ou fournir de mauvaises réponses lorsqu'elle est utilisée dans une requête parallèle. Les fonctions en langage C peuvent en théorie afficher un comportement indéfini si elles sont marquées de façon erronée car le système ne peut pas se protéger comme du code C arbitraire mais, généralement, le résultat ne sera pas pire que pour toute autre fonction. En cas de doute, les fonctions doivent être marqiées comme UNSAFE, ce qui correspond à la valeur par défaut.
Un nombre positif donnant le coût estimé pour l'exécution de la fonction en unité de cpu_operator_cost. Si la fonction renvoie plusieurs lignes, il s'agit d'un coût par ligne renvoyée. Si le coût n'est pas spécifié, une unité est supposée pour les fonctions en langage C et les fonctions internes. Ce coût est de 100 unités pour les fonctions dans tout autre langage. Des valeurs plus importantes feront que le planificateur tentera d'éviter l'évaluation de la fonction aussi souvent que possible.
Un nombre positif donnant le nombre estimé de lignes que la fonction renvoie, information utile au planificateur. Ceci est seulement autorisé pour les fonctions qui renvoient plusieurs lignes (fonctions SRF). La valeur par défaut est de 1000 lignes.
La clause SET fait que le paramètre de configuration indiquée est initialisée avec la valeur précisée au lancement de la fonction, puis restaurée à sa valeur d'origine lors de la sortie de la fonction. SET FROM CURRENT sauvegarde la valeur actuelle du paramètre quand ALTER FUNCTION est exécuté comme valeur à appliquer lors de l'exécution de la fonction.
Si une clause SET est attachée à une fonction, alors les effets de la commande SET LOCAL exécutée à l'intérieur de la fonction pour la même variable sont restreints à la fonction : la valeur précédente du paramètre de configuration est de nouveau restaurée en sortie de la fonction. Néanmoins, une commande SET ordinaire (c'est-à-dire sans LOCAL) surcharge la clause SET, comme il le ferait pour une précédente commande SET LOCAL : les effets d'une telle commande persisteront après la sortie de la fonction sauf si la transaction en cours est annulée.
Voir SET(7) et Chapitre 19, Configuration du serveur pour plus d'informations sur les paramètres et valeurs autorisés.
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.
Il est souvent utile d'utiliser les guillemets dollar (voir Section 4.1.2.4, « Constantes de chaînes avec guillemet dollar ») pour écrire le code de la fonction, au lie des la syntaxe habituelle des guillemets. Sans les guillemets dollar, tout guillemet ou antislash dans la définition de la fonction doit être échappé en les doublant.
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 de la bibliothèque partagée contenant la fonction C compilée et est interprété comme pour une commande ???. La chaîne 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.
Lors d'appels répétés à CREATE FUNCTION se référant au même fichier objet, il est chargé seulement une fois par session. Pour décharger et recharger le fichier (par exemple lors du développement de la fonction), démarrez une nouvelle session.
Façon historique d'indiquer des informations optionnelles concernant la fonction. Les attributs suivants peuvent 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 sont insensibles à la casse.
La lecture de Section 37.3, « Fonctions utilisateur » fournit des informations supplémentaires sur l'écriture de fonctions.
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.
La syntaxe SQL complète des types est autorisé pour déclarer les arguments en entrée et la valeur de sortie d'une fonction. Néanmoins, les modificateurs du type de la fonction (par exemple le champ précision pour un numeric) sont ignorés par CREATE FUNCTION. Du coup, par exemple, CREATE FUNCTION foo (varchar(10)) ... est identique à CREATE FUNCTION foo (varchar) ....
Lors du remplacement d'une fonction existante avec CREATE OR REPLACE FUNCTION, il existe des restrictions sur le changement des noms de paramètres. Vous ne pouvez pas modifier le nom de paramètre en entrée déjà affecté mais vous pouvez ajouter des noms aux paramètres qui n'en avaient pas. S'il y a plus d'un paramètre en sortie, vous ne pouvez pas changer les noms des paramètres en sortie car cela changera les noms de colonne du type composite anonyme qui décrit le résultat de la fonction. Ces restrictions sont là pour assurer que les appels suivants à la fonction ne s'arrêtent pas de fonctionner lorsqu'elle est remplacée.
Quelques exemples triviaux pour bien débuter sont présentés ci-après. Pour plus d'informations et d'exemples, voir Section 37.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.
Parce qu'une fonction SECURITY DEFINER est exécutée avec les droits de l'utilisateur qui en est le propriétaire, 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 (par exemple tables, fonctions et opérateurs) 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;
Le but de cette fonction est d'accéder à une table admin.motsdepasse. Mais sans la clause SET ou avec une clause SET mentionnant uniquement admin, la fonction pourrait être transformée en créant une table temporaire nommée motsdepasse.
Avant PostgreSQL™ 8.3, la clause 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. La clause 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(7) 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;
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.
Pour les valeurs par défaut des paramètres, le standard SQL spécifie seulement la syntaxe du mot clé DEFAULT. La syntaxe utilisant = est utilisé dans T-SQL et Firebird.
CREATE FOREIGN TABLE
