regexp_replace puzzle

From: Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: regexp_replace puzzle
Date: 2010-03-10 12:41:54
Message-ID: pu1vfss60d.fsf@srv.protecting.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2010-03-10 12:46:49 Re: How to? Timestamp with timezone.
Previous Message Dimitri Fontaine 2010-03-10 12:07:41 Re: make available C extensions to others