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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
