Re: invalid regular expression: invalid backreference number

Tom Lane wrote:

Thanks for the reply, Tom.
> Jeff Ross <jross@wykids.org> writes:
>
>> This used to work before my upgrade to 8.2.1.
>>
>
> Which version were you using before?
>
>
8.1.x
>> 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:
>
>
Sorry, my bad, but you guessed right.
>>        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
>
>
To debug this I've extracted the code into its own function:


CREATE FUNCTION gen_password() RETURNS text AS $$
DECLARE
  password text;
  chars := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
BEGIN
  FOR i IN 1..9 LOOP
    password := password || SUBSTRING(chars,
ceil(random()*LENGTH(chars))::int, 1);
  END LOOP;
  return password;
END;
$$
LANGUAGE plpgsql;


when I try to generate the function with this I get the following error:

psql -f create_password.sql wykids
psql:create_password.sql:12: LOG:  statement: CREATE FUNCTION
gen_password() RETURNS text AS $$
DECLARE
        password text;
        chars :=
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
BEGIN
        FOR i IN 1..9 LOOP
                password := password || SUBSTRING(chars,
ceil(random()*LENGTH(chars))::int, 1);
        END LOOP;
        return password;
END;
$$
LANGUAGE plpgsql;
psql:create_password.sql:12: ERROR:  invalid type name ""
CONTEXT:  compile of PL/pgSQL function "gen_password" near line 3

I've not been able to figure out this error message at all, and google
hasn't been any help either.  I'm only now learning functions (I
inherited the one that used to work) so if someone can point me in the
general direction I sure would appreciate it.

Thanks,

Jeff Ross