Thread: 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