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
solutionand 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
Documentsnumbered within each Department, I am wondering what is the best approach.  It's easy enough to just slap a
sequencetype 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
attributeof 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
somethinglike 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
appliedin 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@modelint.com> writes:
>> I presume that the create sequence expression wants to see literals instead of variables, right?  I knew I was going
torun 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice