Ce module code le type de données hstore
, qui permet de stocker
des ensembles de paires clé/valeur dans une simple valeur
PostgreSQL. Ce peut être utile dans divers
cas, comme des lignes avec de nombreux attributs rarement examinés ou des données
semi-structurées. Les clés et les valeurs sont de simples chaînes de texte.
Ce module est considéré comme « trusted », c'est-à-dire qu'il peut
être installé par des utilisateurs simples (sans attribut
SUPERUSER
) ayant le droit CREATE
sur la
base de données courante.
hstore
#
La représentation textuelle d'une valeur hstore
, utilisée en
entrée et en sortie, inclut zéro ou plusieurs paires
clé
=>
valeur
séparées par
des virgules. Par exemple :
k => v foo => bar, baz => whatever "1-a" => "anything at all"
L'ordre des paires n'est pas significatif (et peut ne pas être reproduit
en sortie). Les espaces blancs entre les paires ou autour des signes
=>
sont ignorés. Ajoutez des guillemets
pour les clés et valeurs contenant des espaces, virgules, =
ou
>
. Pour inclure un guillemet double ou un antislash
dans une clé ou une valeur, échappez-le avec un antislash.
Chaque clé dans un hstore
est unique. Si vous déclarez un
hstore
avec des clés dupliquées, seule une sera stockée dans
hstore
et il n'y a pas de garantie sur celle qui sera
conservée :
SELECT 'a=>1,a=>2'::hstore; hstore ---------- "a"=>"1"
Une valeur, mais pas une clé, peut être un NULL
SQL. Par
exemple :
key => NULL
Le mot-clé NULL
est insensible à la casse.
Ajoutez des guillemets autour du NULL
pour qu'il soit
traité comme toute autre chaîne.
Gardez en tête que le format texte hstore
, lorsqu'il est
utilisé en entrée, s'applique avant tout guillemet ou
échappement nécessaire. Si vous passez une valeur littérale de type
hstore
via un paramètre, aucun traitement supplémentaire n'est
nécessaire. Par contre, si vous la passez comme constante littérale entre
guillemets, alors les guillemets simples et, suivant la configuration du
paramètre standard_conforming_strings
, les caractères
antislash doivent être échappés correctement. Voir Section 4.1.2.1 pour plus d'informations sur la gestion des
chaînes constantes.
En sortie, mettez toujours des guillemets doubles autour des clés et valeurs, même quand cela n'est pas strictement nécessaire.
hstore
#
Les opérateurs fournis par le module hstore
sont
montrés dans Tableau F.7 et les fonctions
dans Tableau F.8.
Tableau F.7. Opérateurshstore
Operator Description Exemple(s) |
---|
Renvoie la valeur associée à une clé donnée
(
|
Retourne la valeur associée à des clés
(
|
Concatène deux
|
Ce
|
Ce
|
Ce
|
L'opérande gauche contient-il le droit ?
|
L'opérande gauche est-il contenu dans le droit ?
|
Supprime la clé dans l'opérande gauche.
|
Supprime les clés dans l'opérande gauche.
|
Supprime les clés de l'opérande gauche qui correspondent à des paires de l'opérande droit.
|
Remplace les champs de l'opérande gauche (qui doit être un type composite)
avec les valeurs correspondantes du
|
Convertit un
|
Convertit
|
Tableau F.8. hstore
Functions
En plus de ces opérateurs et fonctions, les valeurs du type
hstore
peuvent utiliser des indices, leur permettant ainsi
d'agir comme des tableaux associatifs. Seul un indice simple de type
text
peut être indiqué ; il est interprété comme la clé,
et la valeur correspondante est récupérée ou stockée. Par exemple,
CREATE TABLE mytable (h hstore); INSERT INTO mytable VALUES ('a=>b, c=>d'); SELECT h['a'] FROM mytable; h --- b (1 row) UPDATE mytable SET h['c'] = 'new'; SELECT h FROM mytable; h ---------------------- "a"=>"b", "c"=>"new" (1 row)
Une lecture par indice renvoie NULL
si l'indice est
NULL
ou si la clé n'existe pas dans ce
hstore
. (De ce fait, une lecture par indice n'est pas
fortement différente de l'opérateur ->
.) Une mise à
jour par indice échoue si l'indice est NULL
;
sinon elle remplace la valeur pour cette clé, ajoutant une entrée au
hstore
si la clé n'existe pas déjà.
hstore
est supporté par les index GiST et GIN pour les
opérateurs @>
, ?
,
?&
et ?|
. Par exemple :
CREATE INDEX hidx ON testhstore USING GIST (h); CREATE INDEX hidx ON testhstore USING GIN (h);
La classe d'opérateur GiST gist_hstore_ops
opère une approximation d'un ensemble de clés/valeurs avec une signature bitmap.
Son paramètre optionnel entier, siglen
,
détermine la longueur de la signature en octets.
La longueur par défaut est 16 octets.
Les valeurs valides de longueur de signature vont de 1 à 2024 octets.
Des signatures plus longues mènent à des recherches plus précises
(scannant une partie plus courte de l'index et moins de pages de la table),
au prix d'un index plus gros.
Exemple de création d'un tel index avec une longueur de signature de 32 octets :
CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
hstore
supporte aussi les index btree
ou
hash
pour l'opérateur =
. Cela permet aux
colonnes hstore
d'être déclarées UNIQUE
et
d'être utilisées dans des expressions GROUP BY
,
ORDER BY
et DISTINCT
. L'ordre de tri
pour les valeurs hstore
n'est pas particulièrement utile mais
ces index peuvent servir à des recherches d'équivalence. Créez des
index de comparaisons =
de la façon suivante :
CREATE INDEX hidx ON testhstore USING BTREE (h); CREATE INDEX hidx ON testhstore USING HASH (h);
Ajouter une clé, ou mettre à jour une clé existante avec une nouvelle valeur :
UPDATE tab SET h['c'] = '3';
Voici une autre façon de faire la même chose :
UPDATE tab SET h = h || hstore('c', '3');
Si plusieurs clés doivent être ajoutées ou modifiées en une seule opération, l'approche par concaténation est plus efficace que par indice :
UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
Supprimer une clé :
UPDATE tab SET h = delete(h, 'k1');
Convertir un enregistrement (record
) en un hstore
:
CREATE TABLE test (col1 integer, col2 text, col3 text); INSERT INTO test VALUES (123, 'foo', 'bar'); SELECT hstore(t) FROM test AS t; hstore --------------------------------------------- "col1"=>"123", "col2"=>"foo", "col3"=>"bar" (1 row)
Convertir un type hstore
en un type record
prédéfini :
CREATE TABLE test (col1 integer, col2 text, col3 text); SELECT * FROM populate_record(null::test, '"col1"=>"456", "col2"=>"zzz"'); col1 | col2 | col3 ------+------+------ 456 | zzz | (1 row)
Modifier un enregistrement existant en utilisant les valeurs provenant
d'un hstore
:
CREATE TABLE test (col1 integer, col2 text, col3 text); INSERT INTO test VALUES (123, 'foo', 'bar'); SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s; col1 | col2 | col3 ------+------+------ 123 | foo | baz (1 row)
Le type hstore
, du fait de sa libéralité intrinsèque, peut
contenir beaucoup de clés différentes. C'est à l'application de vérifier
la validité des clés. Les exemples ci-dessous présentent plusieurs
techniques pour vérifier les clés et obtenir des statistiques.
Exemple simple :
SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
En utilisant une table :
CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
Statistiques en ligne :
SELECT key, count(*) FROM (SELECT (each(h)).key FROM testhstore) AS stat GROUP BY key ORDER BY count DESC, key; key | count -----------+------- line | 883 query | 207 pos | 203 node | 202 space | 197 status | 195 public | 194 title | 190 org | 189 ...................
À partir de PostgreSQL 9.0, hstore
utilise une représentation
interne différente des anciennes versions. Cela ne présente aucun obstacle
pour les mises à jour par sauvegarde/restauration car la représentation
textuelle utilisée dans la sauvegarde n'est pas changée.
Dans le cas d'une mise à jour binaire, la compatibilité ascendante est
maintenue en faisant en sorte que le nouveau code reconnaisse les données
dans l'ancien format. Ceci aura pour conséquence une légère pénalité au
niveau des performances lors du traitement de données qui n'auront pas été
modifiées par le nouveau code. Il est possible de forcer une mise à jour de
toutes les valeurs d'une colonne de la table en réalisant la requête
UPDATE
suivante :
UPDATE nom_table SET col_hstore = col_hstore || '';
Une autre façon de le faire :
ALTER TABLE nom_table ALTER col_hstore TYPE col_hstore USING hstorecol || '';
La méthode ALTER TABLE
requiert un verrou de type
ACCESS EXCLUSIVE
sur
la table, mais n'a pas pour résultat un gonflement de la table à cause
d'anciennes versions des lignes.
Des extensions supplémentaires sont disponibles, qui implémentent des
transformations pour le type hstore
pour les langages PL/Perl et
PL/Python. Les extensions pour PL/Perl sont appelées
hstore_plperl
et hstore_plperlu
, pour
les deux versions de PL/Perl. Si vous installez ces transformations et si
vous les spécifiez lors de la création d'une fonction, les valeurs
hstore
sont converties en hachage Perl. L'extension pour
PL/Python s'appelle hstore_plpython3u
. Si vous l'utilisez,
les valeurs hstore
sont converties en dictionnaires Python.
De ces extensions additionnelles, hstore_plperl
est
considéré « trusted » ; pas les autres.
Il est fortement recommandé que les extensions de transformation soient
installées dans le même schéma que hstore
. Sinon, il
existe un risque de sécurité si le schéma d'une extension de
transformation contient des objets définis par un utilisateur hostile.
Oleg Bartunov <oleg@sai.msu.su>
, Moscou, Université de Moscou,
Russie
Teodor Sigaev <teodor@sigaev.ru>
, Moscou, Delta-Soft Ltd.,
Russie
Améliorations supplémentaires par Andrew Gierth <andrew@tao11.riddles.org.uk>
,
Royaume Uni