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

[John R Pierce]

 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general