Hi all - Let's say the user signs up for an account on my site and they need to pick a unique user name. They type in: MikeChristensen However, me and several of my dopplegangers already have accounts. 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: 1) Just tack a random number at the end and check it to make sure it's unique, if not then loop. I might do a few SQL calls but it would work. However, it looks a bit hacky if I say "Do you want to create the account MikeChristensen394783?" 2) I could write a DO...WHILE loop in a SQL function that first checks if the base name is available, and then tacks on a number and increments it until the name is available. However, this gets slower and slower and has to run one query per name. The username "Joe" might require 1,000 queries someday. Maybe this could be optimized by incrementing more each loop. 3) Do something really funky with the schema, attempting to store the next available suffix for each name somewhere in a table. This gets messy the more I think about it. Ick. 4) Do a SELECT on each row that starts with "MikeChristensen" and then trying to append the row count to the end, this might not be exact but it's somewhat intelligent as a starting point. However, this might require some special indexes on this table to quickly scan rows that start with a certain name. However, if I get to the point where this becomes slow then I can worry about it at that point since this would only be run on new account creation and not super speed critical. This post isn't really a how-to question, just looking for someone who wants to toss out a few ideas for avenues to explore.. Thanks!! Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
