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.