Re: BUG #9175: REINDEX on functional index fails

ia(dot)shumilova(at)gmail(dot)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