Re: "Keyed" sequence?

On Thu, Apr 28, 2016 at 1:48 PM, Israel Brewster <israel(at)ravnalaska(dot)net>
wrote:

> >
> > On Apr 28, 2016, at 10:39 AM, Vik Fearing <vik(at)2ndquadrant(dot)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.
>

​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