On Tue, Jul 5, 2011 at 2:42 PM, John Fabiani
<johnf@jfcomputer.com> wrote:
On Tuesday, July 05, 2011 01:11:11 pm Kevin Crain wrote:
> You don't need a loop there. Assuming your order id field is of type
> varchar you can just build the first part of your string and then do a
> count to get the last part using a LIKE comparison:
>
> select count(id_order) + 1 from sometable WHERE id_order LIKE 'O-20110704
> -%';
>
> If you do this inside a function it will be like running it in a
> transaction so you shouldn't have to worry about it being a multi-user
> system.
>
>
>
I like this - looks better than what I'm currently doing. Thanks
It is simpler, but it will result in id collision if two inserts runs at the same time, particularly if the count query takes a while to run, so be prepared to handle that. Make sure you have an index which can satisfy that count query quickly. If you are not using the C locale for your database, that means you must create an index on that column that uses text_pattern_ops or varchar_pattern_ops (depending on if it is text or varchar column) so that postgresql can use the index for that comparison, otherwise LIKE clauses will force a sequential scan of the whole table every time. C locale does byte by byte text comparison, so the special index isn't required.