Thread: invalid regular expression: invalid backreference number

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

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

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

On Sun, 18 Feb 2007, Jeff Ross wrote:

> Tom Lane wrote:
> >
> > 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.]
> >
> 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 :=
[snipped]
> psql:create_password.sql:12: ERROR:  invalid type name ""
> CONTEXT:  compile of PL/pgSQL function "gen_password" near line 3

Given the context and function, I'd say it's complaining because you
didn't put a type after chars and before the := for the initializer.
Changing it to chars text := ... should make that work.
In addition, the default initialized value for password will be a NULL
which probably won't do what you want either, since NULL || something is
NULL, so you probably want password text := '' there.

Jeff Ross <jross@wykids.org> writes:
> 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

You forgot to give a type for the "chars" variable.

> psql:create_password.sql:12: ERROR:  invalid type name ""
> CONTEXT:  compile of PL/pgSQL function "gen_password" near line 3

I agree that this is a pretty awful error message :-( ... will take a
look at whether it can be improved.

            regards, tom lane