From: | Leon Starr <leon_starr(at)modelint(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Best way to create a sequence generator at run time? |
Date: | 2010-09-22 00:06:12 |
Message-ID: | 77980C02-0B1C-4E51-829C-A30E78A436A6@modelint.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2010-09-22 02:20:03 | Re: Table transfer |
Previous Message | Tom Lane | 2010-09-21 21:56:58 | Re: Best way to create a sequence generator at run time? |