Re: Function PostgreSQL 9.2 - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: Function PostgreSQL 9.2
Date
Msg-id 20160421070002.GB21533@hermes.hilbert.loc
Whole thread Raw
In response to Re: Function PostgreSQL 9.2  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From:
Date:
Subject: Re: Is it possible to call Postgres directly?
Next
From: Karsten Hilbert
Date:
Subject: Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created