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

[John R Pierce <pierce@xxxxxxxxxxxx>]

 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.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general