CREATE TYPE nom AS ( nom_attribut type_donnée [, ... ] ) CREATE TYPE nom AS ENUM ( 'label' [, ... ] ) CREATE TYPE nom ( INPUT = fonction_entrée, OUTPUT = fonction_sortie [ , RECEIVE = fonction_réception ] [ , SEND = fonction_envoi ] [ , TYPMOD_IN = type_modifier_input_function ] [ , TYPMOD_OUT = type_modifier_output_function ] [ , ANALYZE = fonction_analyse ] [ , INTERNALLENGTH = { longueurinterne | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignement ] [ , STORAGE = stockage ] [ , LIKE = type_like ] [ , CATEGORY = catégorie ] [ , PREFERRED = préféré ] [ , DEFAULT = défaut ] [ , ELEMENT = élément ] [ , DELIMITER = délimiteur ] ) CREATE TYPE nom
CREATE TYPE enregistre un nouveau type de données utilisable dans la base courante. L'utilisateur qui définit un type en devient le propriétaire.
Si un nom de schéma est précisé, le type est créé dans ce schéma. Sinon, il est créé dans le schéma courant. Le nom du type doit être distinct du nom de tout type ou domaine existant dans le même schéma. Les tables possèdent des types de données associés. Il est donc nécessaire que le nom du type soit également distinct du nom de toute table existant dans le même schéma.
La première forme de CREATE TYPE crée un type composite. Le type composite est défini par une liste de noms d'attributs et de types de données. Pour l'essentiel, il est équivalent au type ligne (NDT : row type en anglais) d'une table, mais l'utilisation de CREATE TYPE permet d'éviter la création d'une table réelle quand seule la définition d'un type est voulue. Un type composite autonome est utile comme type d'argument ou de retour d'une fonction.
La seconde forme de CREATE TYPE crée un type énuméré (enum), comme décrit dans Section 8.7, « Types énumération ». Les types enum prennent une liste de un à plusieurs labels entre guillemets, chacun devant faire moins de NAMEDATALEN octets (64 dans une installation PostgreSQL™ standard).
La troisième forme de CREATE TYPE crée un nouveau type de base (type scalaire). Pour créer un nouveau type de base, il faut être superutilisateur. (Cette restriction est imposée parce qu'une définition de type erronée pourrait embrouiller voire arrêter brutalement le serveur.)
L'ordre des paramètres, dont la plupart sont optionnels, n'a aucune d'importance. Avant de définir le type, il est nécessaire de définir au moins deux fonctions (à l'aide de la commande CREATE FUNCTION). Les fonctions de support fonction_entrée et fonction_sortie sont obligatoires. Les fonctions fonction_réception, fonction_envoi, type_modifier_input_function, type_modifier_output_function et fonction_analyse sont optionnelles. Généralement, ces fonctions sont codées en C ou dans un autre langage de bas niveau.
La fonction_entrée convertit la représentation textuelle externe du type en représentation interne utilisée par les opérateurs et fonctions définis pour le type. La fonction_sortie réalise la transformation inverse. La fonction entrée peut être déclarée avec un argument de type cstring ou trois arguments de types cstring, oid, integer. Le premier argument est le texte en entrée sous la forme d'une chaîne C, le second argument est l'OID du type (sauf dans le cas des types tableau où il s'agit de l'OID du type de l'élément) et le troisième est le typmod de la colonne destination, s'il est connu (-1 sinon). La fonction entrée doit renvoyer une valeur du nouveau type de données. Habituellement, une fonction d'entrée devrait être déclarée comme STRICT si ce n'est pas le cas, elle sera appelée avec un premier paramètre NULL à la lecture d'une valeur NULL en entrée. La fonction doit toujours envoyer NULL dans ce cas, sauf si une erreur est rapportée. (Ce cas a pour but de supporter les fonctions d'entrée des domaines qui ont besoin de rejeter les entrées NULL.) La fonction sortie doit prendre un argument du nouveau type de données, et retourner le type cstring. Les fonctions sortie ne sont pas appelées pour des valeurs NULL.
La fonction_réception, optionnelle, convertit la représentation binaire externe du type en représentation interne. Si cette fonction n'est pas fournie, le type n'accepte pas d'entrée binaire. La représentation binaire est choisie de telle sorte que sa conversion en forme interne soit peu coûteuse, tout en restant portable. (Par exemple, les types de données standard entiers utilisent l'ordre réseau des octets comme représentation binaire externe alors que la représentation interne est dans l'ordre natif des octets de la machine.) La fonction de réception réalise les vérifications adéquates pour s'assurer que la valeur est valide. Elle peut être déclarée avec un argument de type internal ou trois arguments de types internal, integer et oid. Le premier argument est un pointeur vers un tampon StringInfo qui contient la chaîne d'octets reçue ; les arguments optionnels sont les mêmes que pour la fonction entrée de type texte. La fonction de réception retourne une valeur du type de données. Habituellement, une fonction de réception devrait être déclarée comme STRICT si ce n'est pas le cas, elle sera appelée avec un premier paramètre NULL à la lecture d'une valeur NULL en entrée. La fonction doit toujours envoyer NULL dans ce cas, sauf si une erreur est rapportée. (Ce cas a pour but de supporter les fonctions de réception des domaines qui ont besoin de rejeter les entrées NULL.) De façon similaire, la fonction_envoi, optionnelle, convertit la représentation interne en représentation binaire externe. Si cette fonction n'est pas fournie, le type n'accepte pas de sortie binaire. La fonction d'envoi doit être déclarée avec un argument du nouveau type de données et retourner le type bytea. Les fonctions réception ne sont pas appelées pour des valeurs NULL.
À ce moment-là, vous pouvez vous demander comment les fonctions d'entrée et de sortie peuvent être déclarées avoir un résultat ou un argument du nouveau type alors qu'elles sont à créer avant que le nouveau type ne soit créé. La réponse est que le type sera tout d'abord défini en tant que type squelette (shell type), une ébauche de type sans propriété à part un nom et un propriétaire. Ceci se fait en exécutant la commande CREATE TYPE nom sans paramètres supplémentaires. Ensuite, les fonctions d'entrée/sortie peuvent être définies en référençant le squelette. Enfin, le CREATE TYPE avec une définition complète remplace le squelette avec une définition complète et valide du type, après quoi le nouveau type peut être utilisé normalement.
Les fonctions optionnelles type_modifier_input_function et type_modifier_output_function sont nécessaires si le type supporte des modificateurs, c'est-à-dire des contraintes optionnelles attachées à une déclaration de type comme char(5) ou numeric(30,2). PostgreSQL™ autorise les types définis par l'utilisateur à prendre une ou plusieurs constantes ou identifiants comme modifieurs ; néanmoins, cette information doit être capable d'être englobée dans une seule valeur entière positive pour son stockage dans les catalogues système. type_modifier_input_function se voit fourni le modifieur déclaré de la forme d'un tableau de cstring. Il doit vérifier la validité des valeurs et renvoyer une erreur si elles sont invalides. Dans le cas contraire, il renvoie une valeur entière postivequi sera stockée dans la colonne « typmod ». Les modifieurs de type seront rejetés si le type n'a pas de type_modifier_input_function. type_modifier_output_function convertit la valeur typmod integer en une forme correcte pour l'affichage. Il doit renvoyer une valeur de type cstring qui est la chaîne exacte à ajouter au nom du type ; par exemple la fonction de numeric pourrait renvoyer (30,2). Il est permis d'omettre le type_modifier_output_function, auquel cas le format d'affichage par défaut est simplement la valeur typmod stockée entre parenthèses.
La fonction_analyse, optionnelle, calcule des statistiques spécifiques au type de données pour les colonnes de ce type. Par défaut, ANALYZE tente de récupérer des statistiques à l'aide des opérateurs d'« égalité » et d'« infériorité » du type, s'il existe une classe d'opérateur B-tree par défaut pour le type. Ce comportement est inadapté aux types non-scalaires ; il peut être surchargé à l'aide d'une fonction d'analyse personnalisée. La fonction d'analyse doit être déclarée avec un seul argument de type internal et un résultat de type boolean. L'API détaillée des fonctions d'analyses est présentée dans src/include/commands/vacuum.h.
Alors que les détails de la représentation interne du nouveau type ne sont connus que des fonctions d'entrées/sorties et des fonctions utilisateurs d'interaction avec le type, plusieurs propriétés de la représentation interne doivent être déclarées à PostgreSQL™. La première est longueurinterne. Les types de données basiques peuvent être de longueur fixe (dans ce cas, longueurinterne est un entier positif) ou de longueur variable (indiquée par le positionnement de longueurinterne à VARIABLE ; en interne, cela est représenté en initialisant typlen à -1). La représentation interne de tous les types de longueur variable doit commencer par un entier de quatre octets indiquant la longueur totale de cette valeur.
Le drapeau optionnel PASSEDBYVALUE indique que les valeurs de ce type de données sont passées par valeur plutôt que par référence. Les types dont la représentation interne est plus grande que la taille du type Datum (quatre octets sur la plupart des machines, huit sur quelques-unes) ne doivent pas être passés par valeur.
Le paramètre alignement spécifie l'alignement de stockage requis pour le type de données. Les valeurs permises sont des alignements sur 1, 2, 4 ou 8 octets. Les types de longueurs variables ont un alignement d'au moins quatre octets car leur premier composant est nécessairement un int4.
Le paramètre stockage permet de choisir une stratégie de stockage pour les types de données de longueur variable. (Seul plain est autorisé pour les types de longueur fixe.) plain indique des données stockées en ligne et non compressées. Dans le cas d'extended le système essaie tout d'abord de compresser une valeur longue et déplace la valeur hors de la ligne de la table principale si elle est toujours trop longue. external permet à la valeur d'être déplacée hors de la table principale mais le système ne tente pas de la compresser. main autorise la compression mais ne déplace la valeur hors de la table principale qu'en dernier recours. (Ils seront déplacés s'il n'est pas possible de placer la ligne dans la table principale, mais sont préférentiellement conservés dans la table principale, contrairement aux éléments extended et external.)
Le paramètre type_like fournit une méthode alternative pour spécifier les propriétés de représentation de base d'un type de données : les copier depuis un type existant. Les valeurs de longueurinterne, passedbyvalue, alignement et stockage sont copiées du type indiqué. (C'est possible, mais habituellement non souhaité, d'écraser certaines de ces valeurs en les spécifiant en même temps que la clause LIKE.) Spécifier la représentation de cette façon est particulièrement pratique quand l'implémentation de bas niveau du nouveau type emprunte celle d'un type existant d'une façon ou d'une autre.
Les paramètres catégorie et préféré peuvent être utilisés pour aider à contrôler la conversion implicite appliquée en cas d'ambiguïté. Chaque type de données appartient à une catégorie identifiée par un seul caractère ASCII, et chaque type est « préféré » ou pas de sa catégorie. L'analyseur préfèrera convertir vers des types préférés (mais seulement à partir d'autres types dans la même catégorie) quand cette règle peut servir à résoudre des fonctions ou opérateurs surchargés. Pour plus de détails, voir Chapitre 10, Conversion de types. Pour les types qui n'ont pas de conversion implicite de ou vers d'autres types, on peut se contenter de laisser ces paramètres aux valeurs par défaut. Par contre, pour un groupe de types liés entre eux qui ont des conversions implicites, il est souvent pratique de les marquer tous comme faisant partie d'une même catégorie, et de choisir un ou deux des types les « plus généraux » comme étant les types préférés de la catégorie. Le paramètre catégorie est particulièrement utile quand on ajoute un type défini par l'utilisateur à un type interne, comme un type numérique ou chaîne. Toutefois, c'est aussi tout à fait possible de créer des catégories de types entièrement nouvelles. Choisissez un caractère ASCII autre qu'une lettre en majuscule pour donner un nom à une catégorie de ce genre.
Une valeur par défaut peut être spécifiée dans le cas où l'utilisateur souhaite que cette valeur soit différente de NULL pour les colonnes de ce type. La valeur par défaut est précisée à l'aide du mot clé DEFAULT. (Une telle valeur par défaut peut être surchargée par une clause DEFAULT explicite attachée à une colonne particulière.)
Pour indiquer qu'un type est un tableau, le type des éléments du tableau est précisé par le mot clé ELEMENT. Par exemple, pour définir un tableau d'entiers de quatre octets (int4), ELEMENT = int4 est utilisé. Plus de détails sur les types tableau apparaissent ci-dessous.
Pour préciser le délimiteur de valeurs utilisé dans la représentation externe des tableaux de ce type, délimiteur peut être positionné à un caractère particulier. Le délimiteur par défaut est la virgule (,). Le délimiteur est associé avec le type élément de tableau, pas avec le type tableau.
À chaque fois qu'un type défini par un utilisateur est créé, PostgreSQL™ crée automatiquement un type tableau associé dont le nom est composé à partir du type de base préfixé d'un tiret bas et tronqué si nécessaire pour que le nom généré fasse moins de NAMEDATALEN octets. (Si le nom généré est en conflit avec un autre nom, le traitement est répété jusqu'à ce qu'un nom sans conflit soit trouvé.) Ce type tableau créé implicitement est de longueur variable et utilise les fonctions d'entrée et sortie array_in et array_out. Le type tableau trace tout changement dans du type de base pour le propriétaire et le schéma. Il est aussi supprimé quand le type de base l'est.
Pourquoi existe-t-il une option ELEMENT si le système fabrique automatiquement le bon type tableau ? La seule utilité d'ELEMENT est la création d'un type de longueur fixe représenté en interne par un tableau d'éléments identiques auxquels on souhaite accéder directement par leurs indices (en plus de toute autre opération effectuée sur le type dans sa globalité). Par exemple, le type point est représenté par deux nombres à virgule flottante, qui sont accessibles par point[0] et point[1]. Cette fonctionnalité n'est possible qu'avec les types de longueur fixe dont la forme interne est strictement une séquence de champs de longueur fixée. Un type de longueur variable est accessible par ses indices si sa représentation interne généralisée est celle utilisée par array_in et array_out. Pour des raisons historiques (c'est-à-dire pour de mauvaises raisons, mais il est trop tard pour changer) les indices des tableaux de types de longueur fixe commencent à zéro et non à un comme c'est le cas pour les tableaux de longueur variable.
Le nom (éventuellement qualifié du nom du schéma) du type à créer.
Le nom d'un attribut (colonne) du type composite.
Le nom d'un type de données existant utilisé comme colonne du type composite.
Une chaîne représentant le label associé à une valeur du type enum.
Le nom d'une fonction de conversion des données de la forme textuelle externe du type en forme interne.
Le nom d'une fonction de conversion des données de la forme interne du type en forme textuelle externe.
Le nom d'une fonction de conversion des données de la forme binaire externe du type en forme interne.
Le nom d'une fonction de conversion des données de la forme interne du type en forme binaire externe.
Le nom d'une fonction qui convertit un tableau de modifieurs pour le type vers sa forme interne.
Le nom d'une fonction qui convertit la forme interne des modifieurs du type vers leur forme textuelle externe.
Le nom d'une fonction d'analyses statistiques pour le type de données.
Une constante numérique qui précise la longueur en octets de la représentation interne du nouveau type. Supposée variable par défaut.
La spécification d'alignement du stockage du type de données. Peut être char, int2, int4 ou double ; int4 par défaut.
La stratégie de stockage du type de données. Peut être plain, external, extended ou main ; plain par défaut.
Le nom d'un type de données existant dont le nouveau type partagera la représentation. Les valeurs de longueurinterne, passedbyvalue, alignement et stockage sont recopiées à partir de ce type, sauf si elles sont écrasées explicitement ailleurs dans la même commande CREATE TYPE.
Le code de catégorie (un unique caractère ASCII) pour ce type. La valeur par défaut est U pour « user-defined type » (type défini par l'utilisateur). Les autres codes standard de catégorie peuvent être trouvés dans Tableau 44.43, « Codes typcategory ». Vous pouvez aussi choisir d'autres caractères ASCII pour créer vos propres catégories personnalisées.
True si ce type est un type préféré dans sa catégorie de types, sinon false. La valeur par défaut est false. Faites très attention en créant un nouveau type préféré à l'intérieur d'une catégorie existante car cela pourrait créer des modifications surprenantes de comportement.
La valeur par défaut du type de données. Omise, elle est NULL.
Type des éléments du type tableau créé.
Le caractère délimiteur des valeurs des tableaux de ce type.
Comme il n'y a pas de restrictions à l'utilisation d'un type de données une fois qu'il a été créé, créer un type de base est équivalent à donner les droits d'exécution ssur les fonctions mentionnées dans la définition du type. Ce n'est pas un problème habituellement pour le genre de fonctions utiles dans la définition d'un type mais réfléchissez bien avant de concevoir un type d'une façon qui nécessiterait que des informations « secrètes » soient utilisées lors de sa convertion vers ou à partir d'une forme externe.
Avant PostgreSQL™ version 8.3, le nom d'un type tableau généré était toujours exactement le nom du type élément avec un caractère tiret bas (_) en préfixe. (Les noms des types étaient du coup limités en longueur à un caractère de moins que les autres noms.) Bien que cela soit toujours le cas, le nom d'un type tableau peut varier entre ceci dans le cas des noms de taille maximum et les collisions avec des noms de type utilisateur qui commencent avec un tiret bas. Écrire du code qui dépend de cette convention est du coup obsolète. À la place, utilisez pg_type.typarray pour situer le type tableau associé avec un type donné.
Il est conseillé d'éviter d'utiliser des noms de table et de type qui commencent avec un tiret bas. Alors que le serveur changera les noms des types tableau générés pour éviter les collisions avec les noms donnés par un utilisateur, il reste toujours un risque de confusion, particulièrement avec les anciens logiciels clients qui pourraient supposer que les noms de type commençant avec un tiret bas représentent toujours des tableaux.
Avant PostgreSQL™ version 8.2, la syntaxe CREATE TYPE nom n'existait pas. La façon de créer un nouveau type de base était de créer en premier les fonctions paramètres. Dans cette optique, PostgreSQL™ verra tout d'abord le nom d'un nouveau type de données comme type de retour de la fonction en entrée. Le type shell est créé implicitement dans ce cas et il est ensuite référencé dans le reste des fonctions d'entrée/sortie. Cette approche fonctionne toujours mais est obsolète et pourrait être interdite dans une version future. De plus, pour éviter de faire grossir les catalogues de façon accidentelle avec des squelettes de type erronés, un squelette sera seulement créé quand la fonction en entrée est écrit en C.
Dans les versions de PostgreSQL™ antérieures à la 7.3, la création d'un type coquille était habituellement évitée en remplaçant les références des fonctions au nom du type par le pseudotype opaque. Les arguments cstring et les résultats étaient également déclarés opaque. Pour supporter le chargement d'anciens fichiers de sauvegarde, CREATE TYPE accepte les fonctions d'entrées/sorties déclarées avec le pseudotype opaque mais un message d'avertissement est affiché. La déclaration de la fonction est également modifiée pour utiliser les bons types.
Créer un type composite utilisé dans la définition d'une fonction :
CREATE TYPE compfoo AS (f1 int, f2 text); CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ SELECT fooid, fooname FROM foo $$ LANGUAGE SQL;
Cet exemple crée un type énuméré et l'utilise dans la création d'une table :
CREATE TYPE statut_bogue AS ENUM ('nouveau', 'ouvert', 'fermé'); CREATE TABLE bogue ( id serial, description text, status statut_bogue );
Créer le type de données basique box utilisé dans la définition d'une table :
CREATE TYPE box; CREATE FUNCTION ma_fonction_entree_box(cstring) RETURNS box AS ... ; CREATE FUNCTION ma_fonction_sortie_box(box) RETURNS cstring AS ... ; CREATE TYPE box ( INTERNALLENGTH = 16, INPUT = ma_fonction_entree_box, OUTPUT = ma_fonction_sortie_box ); CREATE TABLE myboxes ( id integer, description box );
Si la structure interne de box est un tableau de quatre éléments float4, on peut écrire :
CREATE TYPE box ( INTERNALLENGTH = 16, INPUT = ma_fonction_entree_box, OUTPUT = ma_fonction_sortie_box, ELEMENT = float4 );
ce qui permet d'accéder aux nombres composant la valeur d'une boîte par les indices. Le comportement du type n'est pas modifié.
Créer un objet large utilisé dans la définition d'une table :
CREATE TYPE bigobj ( INPUT = lo_filein, OUTPUT = lo_fileout, INTERNALLENGTH = VARIABLE ); CREATE TABLE big_objs ( id integer, obj bigobj );
D'autres exemples, intégrant des fonctions utiles d'entrée et de sortie, peuvent être consultés dans Section 34.11, « Types utilisateur ».