stable vs. immutable unaccent function - Mailing list 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!