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