Re: Could regexp_matches be immutable?

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

I wish I had kept specific notes on what I was actually trying to do.
I tried to_number first then the expression as seen below. I guess I
saw the error again and assumed it was the same as for to_number.

sk=# BEGIN;
BEGIN
sk=#
sk=# create table t1 (col1 text);
CREATE TABLE
sk=# INSERT INTO t1 values ('Z342432');
INSERT 0 1
sk=# INSERT INTO t1 values ('REW9432');
INSERT 0 1
sk=#
sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1;regexp_matches
----------------3424329432
(2 rows)

sk=#
sk=# create index t1_idx ON t1 ((  (regexp_matches(col1, '(\d+)$'))[1]  ));
ERROR:  index expression cannot return a set
sk=#
sk=# ROLLBACK;
ROLLBACK



It is interesting that "citext" seems to be functional with exactly
the same statements.

sk=# BEGIN;
BEGIN
sk=#
sk=# create table t1 (col1 citext);
CREATE TABLE
sk=# INSERT INTO t1 values ('Z342432');
INSERT 0 1
sk=# INSERT INTO t1 values ('REW9432');
INSERT 0 1
sk=#
sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1;regexp_matches
----------------3424329432
(2 rows)

sk=#
sk=# create index t1_idx ON t1 ((  (regexp_matches(col1, '(\d+)$'))[1]  ));
CREATE INDEX
sk=#
sk=# ROLLBACK;
ROLLBACK



The function regexp_replace(col1, '^[^0-9]+', '') does seem to do the
trick for text.