Re: Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 03:55:50PM -0700, Adrian Klaver wrote:

> > If I am following, this duplicates the information in
> > companies.client_code_increment, in that they both return the last
> > non-user code. Of course this assumes, as David mentioned, that the
> > client is not using a numeric code system. Then you are left trying
> > to figure whether a number is 'your' number or 'their' number?
> >
> >
> >The customer can add any value into users.code:
> >
> > code CHARACTER VARYING,
> >
> >
> >But he also can let it blank/null if he wants to.
> >That's when the trigger do its job.. Put a value (starting in 1000) in
> >that column.
>
> Understood, but what happens if the customer has been using a code of:
>
> ... 998, 999, 1000
>
> They then left the code null on the next two items and your function stuck
> in 1001 and 1002. Then they figured out what they wanted to do with the
> codes on their end but wanted the items to have codes of 1002, 1001 for the
> items you coded 1001, 1002 respectively.
>
> >
> >Of course that has to be unique, as nobody can use the same value of others.
>
> Unique within a customer, which is what your code implied or unique across
> all customers?
>
> >
> >
> >- I was hoping you cans could help me to start doing the function...
>
> Well, I am with David on this, either the customer is totally in charge of
> the codes or you are. The thought of mixing systems gives me a headache.

How about _two_ columns (pseudo code)

.user_picked_code (can be null)
.assigned_code not null serial starts_with 1000

would that help any ? .assigned_code would always be set
from a sequence but .user_picked_code would be _used_ (say,
via a view) IF NOT NULL.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346