How to activate unaccent search

Howto System Administrator
1

Introduction

By default Tryton tries to search for most of the Char fields if the database supports it.

Activate on PostgreSQL

The unaccent search is done in PostgreSQL thanks to the unaccent extension.
You can activate it with:

CREATE EXTENSION unaccent;

The unaccent function cannot be used as an expression in an index because it is not immutable. This is mainly because it depends on a dictionary file on the server.

However it is possible to modify the function with:

ALTER FUNCTION unaccent(text) IMMUTABLE;
ALTER FUNCTION unaccent(regdictionary, text) IMMUTABLE;

or by creating a new function:

CREATE OR REPLACE FUNCTION f_unaccent(regdictionary, text)
  RETURNS text
  LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT f_unaccent('unaccent', $1)
$func$;

or if you do not have the privileges required for C extensions:

for PostgreSQL >= 14

CREATE OR REPLACE FUNCTION immutable_unaccent(value text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT 
RETURN (SELECT unaccent('unaccent', value));

CREATE OR REPLACE FUNCTION immutable_unaccent(dict regdictionary, value text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN (SELECT unaccent(dict, value));

for PostgreSQL < 14

CREATE OR REPLACE FUNCTION immutable_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT unaccent('unaccent', $1)
$func$;

CREATE OR REPLACE FUNCTION immutable_unaccent(regdictionary, text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT unaccent($1, $2)
$func$;
2 Likes