Re: Could regexp_matches be immutable?

Rod Taylor <rod(dot)taylor(at)gmail(dot)com> writes:
> I tried making a functional index based on an expression containing
> the 2 argument regexp_matches() function. Is there a reason why this
> function is not marked immutable instead of normal?

So I went to see about making the changes to remove regex_flavor, and
was astonished to find that all the regex-related functions are already
marked immutable, and AFAICS always have been. This is clearly wrong,
and we would have to fix it if we weren't about to remove the GUC.
(In principle we should advise people to change the markings in existing
databases, but given the lack of complaints it's probably not worth the
trouble --- I doubt many applications change regex_flavor on the fly.)

So, having dismissed my original off-the-cuff answer to Rod, the next
question is what's really going wrong for him. I get this from
a quick trial:

regression=# create table tt1(f1 text, f2 text);
CREATE TABLE
regression=# create index tt1i on tt1(regexp_matches(f1,f2));
ERROR: index expression cannot return a set

IOW the problem is that regexp_matches returns SETOF, not that it's
marked stable (as it should have been). I'm not sure what semantics you
were expecting the index to have, but we don't have any useful support
for indexes on sets.

regards, tom lane