Hi, Given the following function:
CREATE OR REPLACE FUNCTION unaccent_text(text) RETURNS text AS $BODY$ -- unaccent is STABLE, but the indexes must use IMMUTABLE functions. -- comment this line out when calling pg_dump. SELECT unaccent($1); -- Uncomment this line when calling pg_dump. --SELECT ''::text; $BODY$ LANGUAGE sql IMMUTABLE COST 1; Consider the following query as part of a function that takes a single parameter, p_label text: SELECT t.id INTO v_id FROM some_table t WHERE unaccent_text(t.label) % p_label ORDER BY similarity(t.label, p_label) DESC, t.label LIMIT 1; The above code fails; the value for v_id is never set. Without calling * unaccent_text*, the following code works: SELECT t.id INTO v_id FROM some_table t WHERE unaccent(t.label) % p_label ORDER BY similarity(t.label, p_label) DESC, t.label LIMIT 1; I believe this violates the principle of least astonishment. I was astonished that wrapping the unaccent function within another function affects the outcome. What's even more interesting is that the following code, when issued outside of a function, works (by removing the INTO clause): SELECT t.id FROM some_table t WHERE unaccent_text(t.label) % p_label ORDER BY similarity(t.label, p_label) DESC, t.label LIMIT 1; Pretty strange without knowing PostgreSQL internals. :-) # psql --version psql (PostgreSQL) 9.1.9 # uname -a Linux panther 3.2.0-39-generic #62-Ubuntu SMP Thu Feb 28 00:28:53 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux # cat /etc/issue Ubuntu 12.04.2 LTS \n \l As an aside, the comments in the unaccent_text function are accurate: pg_dump cannot dump the database without first commenting out the SELECT unaccent($1); line. Ideas on why this happens, and any work-arounds, are appreciated. Thank you!