Re: 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.