IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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 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 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 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 : IN, OUT ou 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.

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 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 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 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 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. 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 33.6, « Catégories de volatilité 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 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é.

[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 d'indiquer 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 33.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 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 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.

Exemples

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

É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;
        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 surviendrait avant 
        -- 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;