PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Recherche plein texte » Fonctionnalités supplémentaires

12.4. Fonctionnalités supplémentaires

Cette section décrit des fonctions et opérateurs supplémentaires qui sont utiles en relation avec la recherche plein texte.

12.4.1. Manipuler des documents

La Section 12.3.1 a montré comment des documents en texte brut peuvent être convertis en valeurs tsvector. PostgreSQL fournit aussi des fonctions et des opérateurs pouvant être utilisés pour manipuler des documents qui sont déjà au format tsvector.

tsvector || tsvector

L'opérateur de concaténation tsvector renvoie un vecteur qui combine les lexèmes et des informations de position pour les deux vecteurs donnés en argument. Les positions et les labels de poids sont conservés lors de la concaténation. Les positions apparaissant dans le vecteur droit sont décalées par la position la plus large mentionnée dans le vecteur gauche, pour que le résultat soit pratiquement équivalent au résultat du traitement de to_tsvector sur la concaténation des deux documents originaux. (L'équivalence n'est pas exacte, car tout terme courant supprimé de la fin de l'argument gauche n'affectera pas le résultat alors qu'ils auraient affecté les positions des lexèmes dans l'argument droit si la concaténation de texte avait été utilisée.)

Un avantage de l'utilisation de la concaténation au format vecteur, plutôt que la concaténation de texte avant d'appliquer to_tsvector, est que vous pouvez utiliser différentes configurations pour analyser les différentes sections du document. De plus, comme la fonction setweight marque tous les lexèmes du secteur donné de la même façon, il est nécessaire d'analyser le texte et de lancer setweight avant la concaténation si vous voulez des labels de poids différents sur les différentes parties du document.

setweight(vector tsvector, weight "char") returns tsvector

Cette fonction renvoie une copie du vecteur en entrée pour chaque position de poids weight, soit A, soit B, soit C soit D. (D est la valeur par défaut pour les nouveaux vecteurs et, du coup, n'est pas affichée en sortie.) Ces labels sont conservés quand les vecteurs sont concaténés, permettant aux mots des différentes parties d'un document de se voir attribuer un poids différent par les fonctions de score.

Notez que les labels de poids s'appliquent seulement aux positions, pas aux lexèmes. Si le vecteur en entrée se voit supprimer les positions, alors setweight ne pourra rien faire.

length(vector tsvector) returns integer

Renvoie le nombre de lexèmes enregistrés dans le vecteur.

strip(vector tsvector) returns tsvector

Renvoie un vecteur qui liste les mêmes lexèmes que le vecteur donné, mais à qui il manquera les informations de position et de poids. Alors que le vecteur renvoyé est bien moins utile qu'un vecteur normal pour calculer le score, il est habituellement bien plus petit. Le classement par pertinence ne fonctionne pas aussi bien sur les vecteurs stripés que sur les non-stripés. Par ailleurs, l'opérateur tsquery <-> (FOLLOWED BY) ne trouvera jamais de correspondance pour des entrées stripées, puisqu'il ne peut pas déterminer la distance entre deux occurrences de lexèmes dans ce cas.

Une liste complète des fonctions relatives aux tsvector est disponible à Tableau 9.42.

12.4.2. Manipuler des requêtes

La Section 12.3.2 a montré comment des requêtes texte peuvent être converties en valeurs de type tsquery. PostgreSQL fournit aussi des fonctions et des opérateurs pouvant être utilisés pour manipuler des requêtes qui sont déjà de la forme tsquery.

tsquery && tsquery

Renvoie une combinaison AND des deux requêtes données.

tsquery || tsquery

Renvoie une combinaison OR des deux requêtes données.

!! tsquery

Renvoie la négation (NOT) de la requête donnée.

tsquery <-> tsquery

Renvoie une requête qui recherche une correspondance avec la première requête donnée suivie immédiatement par une correspondance avec la seconde requête donnée, en utilisant l'opérateur tsquery <-> (FOLLOWED BY). Par exemple :

SELECT to_tsquery('fat') <-> to_tsquery('cat | rat');
             ?column?
-----------------------------------
 'fat' <-> 'cat' | 'fat' <-> 'rat'
       

tsquery_phrase(query1 tsquery, query2 tsquery [, distance integer ]) returns tsquery

Renvoie une requête qui recherche une correspondance avec la première requête donnée suivie par une correspondance avec la seconde requête donnée, à une distance d'au plus distance lexèmes, en utilisant l'opérateur tsquery <N>. Par exemple :

SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);
  tsquery_phrase
------------------
 'fat' <10> 'cat'
       

numnode(query tsquery) returns integer

Renvoie le nombre de nœuds (lexèmes et opérateurs) dans un tsquery. Cette fonction est utile pour déterminer si la requête (query) a un sens (auquel cas elle renvoie > 0) ou si elle ne contient que des termes courants (auquel cas elle renvoie 0). Exemples :

SELECT numnode(plainto_tsquery('the any'));
NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
 numnode
---------
       0

SELECT numnode('foo & bar'::tsquery);
 numnode
---------
       3
       

querytree(query tsquery) returns text

Renvoie la portion d'un tsquery qui peut être utilisée pour rechercher dans un index. Cette fonction est utile pour détecter les requêtes qui ne peuvent pas utiliser un index, par exemple celles qui contiennent des termes courants ou seulement des négations de termes. Par exemple :

SELECT querytree(to_tsquery('!defined'));
 querytree
-----------

       

12.4.2.1. Ré-écriture des requêtes

La famille de fonctions ts_rewrite cherche dans un tsquery donné les occurrences d'une sous-requête cible et remplace chaque occurrence avec une autre sous-requête de substitution. En fait, cette opération est une version spécifique à tsquery d'un remplacement de sous-chaîne. Une combinaison cible et substitut peut être vue comme une règle de ré-écriture de la requête. Un ensemble de règles de ré-écriture peut être une aide puissante à la recherche. Par exemple, vous pouvez étendre la recherche en utilisant des synonymes (new york, big apple, nyc, gotham) ou restreindre la recherche pour diriger l'utilisateur vers des thèmes en vogue. Cette fonctionnalité n'est pas sans rapport avec les thésaurus (Section 12.6.4). Néanmoins, vous pouvez modifier un ensemble de règles de ré-écriture directement, sans réindexer, alors que la mise à jour d'un thésaurus nécessite une réindexation pour être pris en compte.

ts_rewrite (query tsquery, target tsquery, substitute tsquery) returns tsquery

Cette forme de ts_rewrite applique simplement une seule règle de ré-écriture : target est remplacé par substitute partout où il apparaît dans query. Par exemple :

SELECT ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'c'::tsquery);
 ts_rewrite
------------
 'b' & 'c'
        

ts_rewrite (query tsquery, select text) returns tsquery

Cette forme de ts_rewrite accepte une query de début et une commande SQL select, qui est fournie comme une chaîne de caractères. select doit renvoyer deux colonnes de type tsquery. Pour chaque ligne de résultats du select, les occurrences de la valeur de la première colonne (la cible) sont remplacées par la valeur de la deuxième colonne (le substitut) dans la valeur actuelle de query. Par exemple :

CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
INSERT INTO aliases VALUES('a', 'c');

SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases');
 ts_rewrite
------------
 'b' & 'c'
        

Notez que, quand plusieurs règles de ré-écriture sont appliquées de cette façon, l'ordre d'application peut être important ; donc, en pratique, vous voudrez que la requête source utilise ORDER BY avec un ordre précis.

Considérons un exemple réel pour l'astronomie. Nous étendons la requête supernovae en utilisant les règles de ré-écriture par la table :

CREATE TABLE aliases (t tsquery primary key, s tsquery);
INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));

SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
           ts_rewrite
---------------------------------
 'crab' & ( 'supernova' | 'sn' )
     

Nous pouvons modifier les règles de ré-écriture simplement en mettant à jour la table :

UPDATE aliases SET s = to_tsquery('supernovae|sn & !nebulae') WHERE t = to_tsquery('supernovae');

SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
                 ts_rewrite
---------------------------------------------
 'crab' & ( 'supernova' | 'sn' & !'nebula' )
     

La ré-écriture peut être lente quand il y a beaucoup de règles de ré-écriture, car elle vérifie l'intérêt de chaque règle. Pour filtrer les règles qui ne sont pas candidates de façon évidente, nous pouvons utiliser les opérateurs de contenant pour le type tsquery. Dans l'exemple ci-dessous, nous sélectionnons seulement les règles qui peuvent correspondre avec la requête originale :

SELECT ts_rewrite('a & b'::tsquery,
                  'SELECT t,s FROM aliases WHERE ''a & b''::tsquery @> t');
 ts_rewrite
------------
 'b' & 'c'
     

12.4.3. Triggers pour les mises à jour automatiques

Note

La méthode décrite dans cette section a été rendue obsolète par l'utilisation des colonnes générées stockées, comme décrit dans Section 12.2.2.

Lors de l'utilisation d'une colonne séparée pour stocker la représentation tsvector de vos documents, il est nécessaire de créer un trigger pour mettre à jour la colonne tsvector quand le contenu des colonnes document change. Deux fonctions trigger intégrées sont disponibles pour cela, mais vous pouvez aussi écrire la vôtre.

    tsvector_update_trigger(tsvector_column_name, config_name, text_column_name [, ... ])
    tsvector_update_trigger_column(tsvector_column_name, config_column_name, text_column_name [, ... ])
   

Ces fonctions trigger calculent automatiquement une colonne tsvector à partir d'une ou plusieurs colonnes texte sous le contrôle des paramètres spécifiés dans la commande CREATE TRIGGER. Voici un exemple de leur utilisation :

CREATE TABLE messages (
    title       text,
    body        text,
    tsv         tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');

SELECT * FROM messages;
   title    |         body          |            tsv
------------+-----------------------+----------------------------
 title here | the body text is here | 'bodi':4 'text':5 'titl':1

SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
   title    |         body
------------+-----------------------
 title here | the body text is here
    

Après avoir créé ce trigger, toute modification dans title ou body sera automatiquement reflétée dans tsv, sans que l'application n'ait à s'en soucier.

Le premier argument du trigger doit être le nom de la colonne tsvector à mettre à jour. Le deuxième argument spécifie la configuration de recherche plein texte à utiliser pour réaliser la conversion. Pour tsvector_update_trigger, le nom de la configuration est donné en deuxième argument du trigger. Il doit être qualifié du nom du schéma comme indiqué ci-dessus pour que le comportement du trigger ne change pas avec les modifications de search_path. Pour tsvector_update_trigger_column, le deuxième argument du trigger est le nom d'une autre colonne de table qui doit être du type regconfig. Ceci permet une sélection par ligne de la configuration à faire. Les arguments restants sont les noms des colonnes texte (de type text, varchar ou char). Elles sont incluses dans le document suivant l'ordre donné. Les valeurs NULL sont ignorées (mais les autres colonnes sont toujours indexées).

Une limitation des triggers internes est qu'ils traitent les colonnes de façon identique. Pour traiter les colonnes différemment -- par exemple pour donner un poids plus important au titre qu'au corps -- il est nécessaire d'écrire un trigger personnalisé. Voici un exemple utilisant PL/pgSQL comme langage du trigger :

CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
     setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
     setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE FUNCTION messages_trigger();
    

Gardez en tête qu'il est important de spécifier explicitement le nom de la configuration lors de la création de valeurs tsvector dans des triggers, pour que le contenu de la colonne ne soit pas affecté par des modifications de default_text_search_config. Dans le cas contraire, des problèmes surviendront comme des résultats de recherche changeant après une sauvegarde/restauration.

12.4.4. Récupérer des statistiques sur les documents

La fonction ts_stat est utile pour vérifier votre configuration et pour trouver des candidats pour les termes courants.

    ts_stat(sqlquery text, [ weights text, ]
            OUT word text, OUT ndoc integer,
            OUT nentry integer) returns setof record
   

sqlquery est une valeur de type texte contenant une requête SQL qui doit renvoyer une seule colonne tsvector. ts_stat exécute la requête et renvoie des statistiques sur chaque lexème (mot) contenu dans les données tsvector. Les colonnes renvoyées sont :

  • word text -- la valeur d'un lexème

  • ndoc integer -- le nombre de documents (tsvector) où le mot se trouve

  • nentry integer -- le nombre total d'occurrences du mot

Si weights est précisé, seules les occurrences d'un de ces poids sont comptabilisées.

Par exemple, pour trouver les dix mots les plus fréquents dans un ensemble de document :

SELECT * FROM ts_stat('SELECT vector FROM apod')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
    

De la même façon, mais en ne comptant que les occurrences de poids A ou B :

SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;