Re: Query to get the "next available" unique suffix for a name

  On 09/27/10 6:36 PM, Mike Christensen wrote:
> Thus, the users table already has:
>
> MikeChristensen1
> MikeChristensen2
> MikeChristensen3
> MikeChristensen4
>
> I want to write a SQL query that figures out that MikeChristensen5 is
> the next available username and thus suggest it.  Here's some things I
> could do:

bogo pseudocode.
$n is 'MikeChristensen'

     table users {
         uid serial,
         username text unique,
         userroot text,
         namesuffix integer,
         ...
         }

     sql.exec ("begin;");
     sql.exec ("select max(namesuffix) from users where userroot=$1;", $n);
     $i = sql.result(1);
     newname = $n.$i;
     sql.exec("insert ......");
     sql,exec("commit;");



of course, you need to deal with an unique constraint exception on that
insert, and rollback and retry the whole thing.