Re: Best way to create a sequence generator at run time?

Thanks, Tom! Makes perfect sense.

I would like to do something now, just a bit more advanced, but I can't help thinking that there must be a standard solution and thought maybe you could point me in the right direction.

I want to number a relvar (table) as a sub sequence of another relvar. So if I have Department and Document with Documents numbered within each Department, I am wondering what is the best approach. It's easy enough to just slap a sequence type on Department.Number. But what about Document? I would need a new sequence object for each relation (row) in Department, since each Department handles its own document sequence. It's almost like I should have an attribute of Department of type 'sequence generator'. Is that doable?

Otherwise, I am thinking of just using a naming scheme where each Department's sequence generator would be named something like this: <dept_<dept_number>_docnumbering_seq

The question then is simply: Am I heading down the right road? Or is there a simpler solution that is commonly applied in this case or some cool trick I am missing?

Thanks. - Leon

On Sep 21, 2010, at 2:56 PM, Tom Lane wrote:

> Leon Starr <leon_starr(at)modelint(dot)com> writes:
>> I presume that the create sequence expression wants to see literals instead of variables, right? I knew I was going to run into this situation sooner or later. What should I be doing here?
>
> You need to construct the CREATE SEQUENCE command as a string then
> EXECUTE it. CREATE SEQUENCE, like most other utility commands, doesn't
> handle parameters well.
>
> regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice