stable vs. immutable unaccent function

From: Thangalin <thangalin(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: stable vs. immutable unaccent function
Date: 2013-05-05 02:05:04
Message-ID: CAANrE7rpWjvZgdKX_hArNp8ynUezZ-Ehp8QEqC7hwfXuNqa91g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message frank 2013-05-05 18:18:02 BUG #8135: current_setting('DateStyle'); does not reflect User setting
Previous Message jinu.mail 2013-05-04 20:01:53 BUG #8134: initdb failed to execute