2010/3/10 Harald Fuchs <hari.fuchs@gmail.com>:
> I've got a problem with regexp_replace which I could reduce to the following:
>
> CREATE FUNCTION digest(text, text) RETURNS bytea
> LANGUAGE c IMMUTABLE STRICT
> AS '$libdir/pgcrypto', 'pg_digest';
>
> CREATE FUNCTION sha224enc(text) RETURNS text AS $$
> BEGIN
> RAISE WARNING 'arg=»%«', $1;
> RETURN encode(digest($1, 'sha224'), 'hex');
> END;
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> CREATE TABLE t1 (
> id serial NOT NULL,
> val text NOT NULL,
> PRIMARY KEY (id)
> );
>
> INSERT INTO t1 (val) VALUES ('d111111');
> INSERT INTO t1 (val) VALUES ('xd222222');
> INSERT INTO t1 (val) VALUES ('x d333333');
>
> SELECT val,
> regexp_replace(val,
> '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$',
> '\1' || '»\2«='|| sha224enc('\2') || '\3', 'i')
> FROM t1
> WHERE val ~*
> '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$';
>
> (I want to replace patterns within a string by their SHA-224 hash.)
> However, when I run this example I get:
>
> WARNING: arg=»\2«
> val | regexp_replace
> -----------+----------------------------------------------------------------------
> d111111 | »d111111«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
> x d333333 | x »d333333«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
> (2 rows)
>
> i.e. the first '\2' gets properly expanded by the second paren match,
> but the second '\2' doesn't get expanded.
>
> What am I overlooking?
>
Use g flag.
"Flag g causes the function to find each match in the string, not only
the first one, and return a row for each such match." [1]
Osvaldo
[1] http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
