--- In firebird-support@yahoogroups.com, Jonathan Neve wrote:

> Hi!
>
> Generators are nice. The main thing that's good about them is that
> they're transaction independant. Thus, they are perfect for
> generating a sequence of consecutive interger values. However, it's
> difficult to use a generator as a primary key in many cases, because
> it is not related to anything : it's just a sequence of numbers.
> Many of my customers ask for invoice numbering that includes that
> year and month of the invoice, and, within each month, an
> independant counter, like this : "YYMM0001". So for such situations,
> it's more difficult to generate a corrrect PK value.
>
> The solution would be to use a table with one counter per
> year/month, and to simply increment this counter. However, this
> approach has the big disadvantage that it's dependant on the
> transaction's view of the table, which can obviously cause problems.

With FB 1.5 I'd say there is one alternative. Create a stored
procedure that calls EXECUTE STATEMENT, referring to a generatorname
supplied as an input parameter. You could also provide for a way to
create new generators whenever a new year started.

> So what I would like to suggest would be a solution similar to
> generators, but allowing to have a variable number of independant
> counters, which could then be accessed like a hash table.
>
> Something like this:
>
> CREATE HASH_GENERATOR GEN_TABLE_A;
> SELECT HASH_GEN_ID(GEN_TABLE_A, 'YYMM', 1) FROM RDB$DATABASE;
>
> What do you think?

I dislike the syntax and wonder if it will be difficult to implement.
A somewhat less flexible alternative could be
CREATE GENERATORS GEN_FIXED_ARRAY(1..99)
which could be called using e.g.
SELECT GEN_ID(GEN_FIXED_ARRAY(25), 1) FROM RDB$DATABASE
but I am happy with generators as they work today.

Set