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