Re: [GENERAL] invalid regular expression: invalid backreference number
Jeff Ross <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] invalid regular expression: invalid backreference number
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] invalid regular expression: invalid backreference number
Tom Lane wrote: Thanks for the reply, Tom. Jeff Ross <[EMAIL PROTECTED]> 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: ERROR: invalid regular expression: invalid backreference number 2007-02-15 15:32:57.264729500 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] invalid regular expression: invalid backreference number
Jeff Ross <[EMAIL PROTECTED]> 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: > ERROR: invalid regular expression: invalid backreference > number > 2007-02-15 15:32:57.264729500 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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: ERROR: invalid regular expression: invalid backreference number 2007-02-15 15:32:57.264729500 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
