Search Postgresql Archives

invalid regular expression: invalid backreference number

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux