Re: Function PostgreSQL 9.2 - Mailing list pgsql-general

From David G. Johnston
Subject Re: Function PostgreSQL 9.2
Date
Msg-id CAKFQuwZVAYY4oeY82nmc4EtJYHC71U41SxksJxiXTQH9ej+UOQ@mail.gmail.com
Whole thread Raw
In response to Re: Function PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
List pgsql-general
On Wed, Apr 20, 2016 at 2:51 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
1 - The customer can add any value into users.code column
2 - The customer can chose between add or not add the value on users.code column
3 - 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.

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Function PostgreSQL 9.2
Next
From: Kevin Grittner
Date:
Subject: Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created