Re: BUG #9175: REINDEX on functional index fails

ia.shumilova@gmail.com writes:
> -- function for tree structure assembling
> -- outputs something like 'level0/level1/level2/leaf'
> CREATE OR REPLACE FUNCTION taxonomy_string(tree_detail)
>   RETURNS text AS
> $BODY$
>   SELECT
>     COALESCE(string_agg(tax_entry, '/'), '')
>   FROM
>     (
>     SELECT
>       tax_entry
>     FROM
>       tree_detail
>     WHERE
>       _left <= $1._left
>       AND _right >= $1._right
>       AND tree_master_id = $1.tree_master_id
>     ORDER BY _level
>     ) u
> $BODY$
>   LANGUAGE sql IMMUTABLE;

I don't have a whole lot of sympathy for a bug report that involves
a function claimed to be IMMUTABLE when that marking is a lie.
This function selects from tree_detail, so it can at best be claimed
to be STABLE; which means you cannot use it in an index.

I think the proximate cause of the reported error is that once the
index exists, the query in the function tries to use it; and then
that usage will fail when the index is in mid-rewrite.  It might
be worth trying to make that fail more cleanly, if index functions
that depend on selecting from their table were a supported thing;
but they aren't and never will be.

You might be able to get the results you want by including the
taxonomy_string() function in a materialized view over the table.

            regards, tom lane