Adrian Klaver wrote:
> On 04/28/2016 11:48 AM, Israel Brewster wrote:
>>>
>>> On Apr 28, 2016, at 10:39 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
>>>
>>> On 04/28/2016 08:30 PM, Israel Brewster wrote:
>>>> This is probably crazy talk, but in Postgresql is there any way to have
>>>> a "keyed" sequence? That is, I have a table with a list of departments.
>>>> While *relatively* static, technically speaking this is a dynamic
>>>> list -
>>>> someone certainly could decide to add a department, or combine two
>>>> departments into one new one, whatever. In any case, I would ideally
>>>> like to have a sequence per department, but since the list is dynamic
>>>> it's not as simple as creating 33 independent sequences and then having
>>>> a lookup table somewhere, although I guess I could implement something
>>>> of the sort with triggers.
>>>
>>> What would be the point of this? Why not just one sequence for all
>>> departments?
>>
>> continuity and appearance, not to mention simple logical progression.
>> In this case, the sequence is being used to generate a PO number.
>> Company style indicates that a PO number is a department code followed
>> by a unique number. With one sequence for all departments, you could
>> (will) end up with discontinuous PO numbers in any given department.
>> It would be nice if, after issuing PO number 15-1, the next PO in
>> department 15 was 2, if for no other reason than the accounting
>> department could easily see that they aren't missing any. With one
>> sequence, there will quite likely not be a PO number 2 for any given
>> department, so that department has no easy way to keep track of their
>> PO's based on PO number.
>
> Here is a similar idea:
>
> http://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com
>
>>
BAM!! You beat me to it!!
I have repeated that pattern multiple times and it is the exact use case
the OP has.