>
> On Apr 28, 2016, at 10:39 AM, Vik Fearing <vik@xxxxxxxxxxxxxx> 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.
Have you looked at an UPDATE ... RETURNING ... to generate a new PO number? I don't know the structure of the table involved, but something like the following occurs to me:
UPDATE table SET DEPT_LAST_PO = 1 + DEPT_LAST_PO RETURNING DEPT_LAST_PO;
example transcript:
# create table dept (dept_name text, dept_last_po int default 0);
CREATE TABLE
# insert into dept values('finance'); -- default dept_last_po to 0
INSERT 0 1
# update dept set dept_last_po = 1 + dept_last_po where dept_name='finance' returning dept_last_po;
dept_last_po
--------------
1
(1 row)
UPDATE 1
> --
> Vik Fearing +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
>
--
The unfacts, did we have them, are too imprecisely few to warrant our certitude.
Maranatha! <><
John McKown
John McKown
