Entonces, después de la reciente filtración de Twitch, todos están discutiendo sus fragmentos favoritos de código incorrecto. Un bit que se destacó fue una sola declaración SQL monstruosa para detectar "términos ilegales". Eso me hizo pensar en cómo implementar esto "correctamente".
Entonces, tiene una tabla de cadenas con las que desea hacer coincidir las subcadenas, ¿cómo escribe esto en PL/pgSQL? Supongo que cualquier implementación de SQL debería tener capacidades de procedimiento para crear funciones/procedimientos para dicha metaprogramación, básicamente creando y ejecutando SQL como el siguiente:
admin@localhost:words> SELECT 'XabcY' LIKE '%abc%' OR 'XabcY' LIKE '%xyz%' as matches; +-----------+ | matches | |-----------| | True | +-----------+ Entonces, para ser más específicos, dada una lista de cadenas en la tabla disallowed :
| illegal_string | |----------------| | stupid | | witless | | moron | | commie-lover | ¿Cómo crearía una consulta dinámica en PL/pgSQL que, cuando se ejecuta, devuelve verdadero si alguno de estos coincide con una cadena determinada? No es necesario usar ILIKE en Twitch para verificar si la palabra dada contenía la subcadena, por lo que usar la position también está bien, pero debería ser eficaz/ajustable usando índices de gin y demás.
Hay dos posibilidades:
LIKE ANY(array) postgres=# select 'Ahoj' like any (ARRAY['Ah%', 'Na%']); ┌──────────┐ │ ?column? │ ╞══════════╡ │ t │ └──────────┘ (1 row) postgres=# select 'Nazdar' like any (ARRAY['Ah%', 'Na%']); ┌──────────┐ │ ?column? │ ╞══════════╡ │ t │ └──────────┘ (1 row) postgres=# select 'Ahoj' ~ '^(Ah|Na)'; ┌──────────┐ │ ?column? │ ╞══════════╡ │ t │ └──────────┘ (1 row) postgres=# select 'Nazdar' ~ '^(Ah|Na)'; ┌──────────┐ │ ?column? │ ╞══════════╡ │ t │ └──────────┘ (1 row)Entonces, al final, no necesita SQL dinámico.
También hay sintaxis ANSI/SQL:
postgres=# select 'Nazdar' similar to '(Ah|Na)%'; ┌──────────┐ │ ?column? │ ╞══════════╡ │ t │ └──────────┘ (1 row)Así que puedes escribir algo como:
DECLARE pw text[]; BEGIN pw := (SELECT array_agg('%' || disallowed || '%' FROM disallowed); IF EXISTS(SELECT * FROM foo WHERE c LIKE ANY (pw)) THEN RAISE NOTICE 'there are some disallowed words'; END IF; ...No estoy seguro acerca de una actuación. En una tabla más grande, necesita un índice de trigramas, o tal vez sea mejor usar texto completo en lugar de búsqueda de subcadenas.
No creo que necesite PL/pgSQL o SQL dinámico para esto. Cree un conjunto de texto de la tabla de palabras no permitidas y compárelo con los elementos establecidos como expresiones regulares. Tal vez no tenga mucho rendimiento usando la magia de expresiones regulares, pero espero que sea sencillo. Por supuesto, la consulta siguiente se puede parametrizar.
select '<text to examine>' ~* any(select illegal_string from disallowed) as rude; select 'You Stupido MORONE!' ~* any(select illegal_string from disallowed) as rude; -- yields true.Es posible que desee restringir la búsqueda solo a palabras completas. Luego juega con el conjunto de expresiones regulares y dale forma de esta manera:
select 'You Stupido MORONE!' ~* any(select '\m'||illegal_string||'\M' from disallowed) as rude; -- yields falseviolín SQL aquí