1 - The customer can add any value into users.code column2 - The customer can chose between add or not add the value on users.code column3 - If users.code is null (because the customer's chosen not to add any value in there), a trigger/function has to do the job.4 - the function/trigger add the next available value, which the default is 1000 and it's stored on companies.client_code_increment
And if the value you compute happens to be one the client choose themselves what happens?
The procedural logic for this shouldn't be too complicated. The documentation can give you syntax and capabilities.
The bigger problem is an ill-defined process and unexpected conflicts both with the data itself (my comment above) and with concurrency.
I wouldn't give the user a choice. Either they always pick values or they never pick values. I'd give them a function they can choose to utilize to auto-generate values if they do not wish to select their own. Document the algorithm and if they choose to mix-and-match they should avoid conflicting algorithms.
I'd probably use CREATE SEQUENCE and pull numbers from that using nextval instead of trying to code a custom sequence generator with meta-data stored on a company column. I'd relax the "one sequence per company" behavior if possible.
David J.
