From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeff Ross <jross(at)wykids(dot)org> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: invalid regular expression: invalid backreference number |
Date: | 2007-02-16 06:35:28 |
Message-ID: | 11557.1171607728@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jeff Ross <jross(at)wykids(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2007-02-16 06:48:26 | Re: Problem writing sql statement.... |
Previous Message | Tom Lane | 2007-02-16 06:10:46 | Re: Database performance comparison paper. |