PostgreSQL: fonction d'échappement D'Expression régulière

pour renoncer à lire le problème entier, ma question de base est:

y a-t-il une fonction dans PostgreSQL pour échapper aux caractères d'expression régulière d'une chaîne?

j'ai examiné la documentation mais je n'ai pas pu trouver une telle fonction.

voici le problème complet:

dans une base de données PostgreSQL, j'ai une colonne avec des noms uniques. J'ai aussi avoir un processus qui insère périodiquement des noms dans ce champ, et, pour éviter les doublons, s'il a besoin d'entrer un nom qui existe déjà, il ajoute un espace et des parenthèses avec un nombre à la fin.

i.e. Nom, Nom (1), Nom (2), Nom (3), etc.

en l'état, j'utilise le code suivant pour trouver le numéro suivant à ajouter dans la série (écrit en plpgsql): 

var_name_id := 1;

SELECT CAST(substring(a.name from E'((d+))$') AS int)
INTO var_last_name_id
FROM my_table.names a
WHERE a.name LIKE var_name || ' (%)'
ORDER BY CAST(substring(a.name from E'((d+))$') AS int) DESC
LIMIT 1;

IF var_last_name_id IS NOT NULL THEN
    var_name_id = var_last_name_id + 1;
END IF;

var_new_name := var_name || ' (' || var_name_id || ')';

( var_name contient le nom que j'essaie insérer.)

cela fonctionne pour le moment, mais le problème réside dans le WHERE déclaration: 

WHERE a.name LIKE var_name || ' (%)'

ce contrôle ne vérifie pas que le % en question est un nombre, et il ne tient pas compte des parenthèses multiples, comme dans quelque chose comme" Name (((1))", et si l'un ou l'autre cas existait, une exception cast serait lancée.

l'énoncé WHERE doit vraiment ressembler à quelque chose comme: 

WHERE a.r1_name ~* var_name || E' (d+)'

mais var_name pourrait contenir des caractères d'expression régulière, ce qui conduit à la question ci-dessus: y a-t-il une fonction dans PostgreSQL qui échappe aux caractères d'expression régulière dans une chaîne, donc je pourrais faire quelque chose comme: 

WHERE a.r1_name ~* regex_escape(var_name) || E' (d+)'

toutes les suggestions sont très appréciées, y compris un possible remaniement de ma solution de nom dupliqué.

5
demandé sur Erwin Brandstetter 2011-02-28 18:36:58

3 réponses

Que diriez-vous d'essayer quelque chose comme ça, en substituant var_name à mon code dur 'John Bernard' : 

create table my_table(name text primary key);
insert into my_table(name) values ('John Bernard'), 
                                  ('John Bernard (1)'), 
                                  ('John Bernard (2)'), 
                                  ('John Bernard (3)');


select max(regexp_replace(substring(name, 13), ' |\(|\)', '', 'g')::integer+1) 
from my_table 
where substring(name, 1, 12)='John Bernard' 
      and substring(name, 13)~'^ \([1-9][0-9]*\)$';

 max
-----
   4
(1 row)

une mise en garde: je suppose qu'un seul utilisateur accède à la base de données pendant que ce processus est en cours (et vous aussi dans votre approche). Si tel n'est pas le cas, l'approche max(n)+1 ne sera pas la bonne.

1
répondu 2011-02-28 17:05:27

pour répondre À la question dans la partie supérieure:

expression Régulière échapper la fonction

commençons par une liste complète de caractères avec un sens particulier dans expression régulière motifs: 

!$()*+.:<=>?[\]^{|}-

enveloppé dans une bracket expression la plupart d'entre eux perdent leur signification particulière - avec quelques exceptions:

  • - doit être le premier ou le dernier ou il indique un gamme de caractères.
  • ] et \ doivent être échappés avec \ .

après l'ajout de la saisie des parenthèses pour la référence arrière ) ci-dessous, nous obtenons ce motif regexp: 

([!$()*+.:<=>?[\\]^{|}-])

en l'utilisant, cette fonction échappe à tous les caractères spéciaux avec un antislash ( \ ) - supprimant ainsi la signification spéciale: 

CREATE OR REPLACE FUNCTION f_regexp_escape(text)
  RETURNS text AS
$func$
SELECT regexp_replace(, '([!$()*+.:<=>?[\\]^{|}-])', '\', 'g')
$func$  LANGUAGE sql IMMUTABLE;

Démo 

SELECT f_regexp_escape('test(1) > Foo*');

Retourne: 

test\(1\) \> Foo\*

et tandis que: 

SELECT 'test(1) > Foo*' ~ 'test(1) > Foo*';

renvoie FALSE , qui peut venir comme une surprise pour les utilisateurs naïve, 

SELECT 'test(1) > Foo*' ~ f_regexp_escape('test(1) > Foo*')

Renvoie TRUE comme il faut maintenant.

LIKE fonction d'évacuation

pour complétude, le pendentif LIKE motifs, où seulement trois caractères sont spéciaux: 

\%_

Le manuel:

le caractère d'échappement par défaut est le backslash mais un autre peut être sélectionné en utilisant la clause ESCAPE .

cette fonction suppose la valeur par défaut: 

CREATE OR REPLACE FUNCTION f_like_escape(text)
  RETURNS text AS
$func$
SELECT replace(replace(replace(
         , '\', '\')  -- must come 1st
         , '%', '\%')
         , '_', '\_');
$func$  LANGUAGE sql IMMUTABLE;

nous pourrions utiliser le plus élégant regexp_replace() ici, aussi, mais pour quelques caractères, une cascade de replace() fonctions est plus rapide.

Démo 

SELECT f_like_escape('20% \ 50% low_prices');

Retourne: 

20\% \ 50\% low\_prices
4
répondu Erwin Brandstetter 2018-04-21 23:12:21

pouvez-vous changer le schéma? Je pense que le problème disparaîtrait si vous pouviez utiliser une clé primaire composite: 

name text not null,
number integer not null,
primary key (name, number)

il devient alors du devoir de la couche d'affichage D'afficher Fred #0 comme" Fred", Fred #1 comme" Fred (1)", etc.

si vous voulez, vous pouvez créer une vue pour ce devoir. Voici les données: 

=> select * from foo;
  name  | number 
--------+--------
 Fred   |      0
 Fred   |      1
 Barney |      0
 Betty  |      0
 Betty  |      1
 Betty  |      2
(6 rows)

Le point de vue: 

create or replace view foo_view as
select *,
case
  when number = 0 then
    name
  else
    name || ' (' || number || ')'
end as name_and_number
from foo;

et le résultat: 

=> select * from foo_view;
  name  | number | name_and_number 
--------+--------+-----------------
 Fred   |      0 | Fred
 Fred   |      1 | Fred (1)
 Barney |      0 | Barney
 Betty  |      0 | Betty
 Betty  |      1 | Betty (1)
 Betty  |      2 | Betty (2)
(6 rows)
0
répondu Wayne Conrad 2011-02-28 20:51:21