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!
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 |