Re: invalid regular expression: invalid backreference number

Jeff Ross <jross@wykids.org> writes:
> This used to work before my upgrade to 8.2.1.

Which version were you using before?

> 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

You could have helped us out by mentioning exactly which line was line
58 ... but I'm guessing it's this one:

>        gen_pp_password := gen_pp_password || SUBSTRING(chars,
> ceil(random()*LENGTH(chars)), 1);2007-02-15 15:32:57.264727500

Since ceil() produces float8 which does not implicitly cast to int,
this call has probably never done what you thought --- AFAICS it will
cast all the arguments to text and invoke substring(text,text,text)
which treats its second argument as a SQL99 regular expression.
I doubt that it's useful to figure out exactly what changed to make
it fail more obviously than before --- I think the problem is that
you'd better cast the ceil() result to int.

[ObRant: still another example of why implicit casts to text are evil.]

            regards, tom lane