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.
