From: | Jeff Ross <jross(at)wykids(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | invalid regular expression: invalid backreference number |
Date: | 2007-02-15 22:58:24 |
Message-ID: | 45D4E5A7.9060702@wykids.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've got a function that generates usernames and passwords on insert if
they haven't yet been set. The code block is:
-- create a new username for new people
IF (LENGTH(COALESCE(new_pp_username, '')) = 0) THEN
LOOP
gen_pp_username := LOWER(SUBSTRING(new_pp_first_name from 1 for
2)) || LOWER(SUBSTRING(new_pp_last_name from 1 for 8)) ||
round(random()*100);
gen_pp_username := regexp_replace(gen_pp_username, E'\\W', '', 'g');
EXIT WHEN ((SELECT COUNT(*) FROM people WHERE pp_username =
gen_pp_username AND pp_provisional_p='f') = 0);
END LOOP;
ELSE
gen_pp_username := new_pp_username;
END IF;
-- create a new password if there is none
IF (LENGTH(COALESCE(new_pp_password, '')) = 0) THEN
chars :=
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
FOR i in 1..8 LOOP
gen_pp_password := gen_pp_password || SUBSTRING(chars,
ceil(random()*LENGTH(chars)), 1);2007-02-15 15:32:57.264727500
END LOOP;
ELSE
gen_pp_password := new_pp_password;
END IF;
This used to work before my upgrade to 8.2.1.
The error the function now throws is:
<jross%wykids>ERROR: invalid regular expression: invalid backreference
number
2007-02-15 15:32:57.264729500 <jross%wykids>CONTEXT: SQL function
"substring" statement 1
2007-02-15 15:32:57.264730500 PL/pgSQL function "set_people" line 58
at assignment
I've futzed around with the various ways I can call substring, but I
don't understand why this is throwing the error.
Any help would be greatly appreciated!
Jeff Ross
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-02-15 23:24:09 | Re: postgresql 8.2 Installation error at "gmake" |
Previous Message | Ron Johnson | 2007-02-15 22:46:21 | Re: Problem writing sql statement.... |