BUG #15272: creating an index function terminate with error
The following bug has been logged on the website:
Bug reference: 15272
Logged by: karim loumour
Email address: loumour@gmail.com
PostgreSQL version: 10.4
Operating system: RHEL 7
Description:
I created the function bellow :
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text AS $$
SELECT lower(unaccent('unaccent', $1))
$$ LANGUAGE sql IMMUTABLE;
I can call this function correctly :
select upper(f_unaccent(nom)) from geoname limit 10;
upper
----------------------
CANAL LLISA
TORRENT LLIMOIS
BARRANC DEL LLEMPO
CASA LLECSIA
BORDA DEL LLECSIA
PONT DEL LLARG
FONTANAL LLARG
CASA LLARG
CANAL DEL LLARG
TORRENT DE LA LLAMPA
(10 rows)
But, when i need to create an index using that function, i have the
following error:
dref=# create index geo_test on geoname(upper(f_unaccent(nom))) ;
ERROR: function unaccent(unknown, text) does not exist
LINE 2: SELECT lower(unaccent('unaccent', $1))
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY:
SELECT lower(unaccent('unaccent', $1))
CONTEXT: SQL function "f_unaccent" during inlining
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
I created the function bellow :
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text AS $$
SELECT lower(unaccent('unaccent', $1))
$$ LANGUAGE sql IMMUTABLE;
But, when i need to create an index using that function, i have the
following error:
dref=# create index geo_test on geoname(upper(f_unaccent(nom))) ;
ERROR: function unaccent(unknown, text) does not exist
You need to schema-qualify the function name, or else add a SET clause
to force an appropriate search_path setting. PG did not use to be
picky about that, but it is since CVE-2018-1058. In any case, the
old behavior was very dangerous since the index function's behavior
could vary depending on the prevailing search path.
regards, tom lane