invalid regular expression: invalid backreference number

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

Responses

Browse pgsql-general by date

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