Thread: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Justin Clift
Date:
Hi all, Am doing some work with sequences at the moment, and I'm finding it would be useful to have sequences which use an increment amount decided by a function call, instead of just a straight integer amount (as we presently do). For my example, I'd use this to add random positive increments (specifically to avoid easy predictability of the sequence), but it would be quite flexible. i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10); Could do the same for the START, MINVALUE and MAXVALUE parameters also. Would others also benefit from this alteration? If so, we might like to ask for it to be added to Bruce's TODO list. Regards and best wishes, Justin Clift
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Bruce Momjian
Date:
> Hi all, > > Am doing some work with sequences at the moment, and I'm finding it would be > useful to have sequences which use an increment amount decided by a function > call, instead of just a straight integer amount (as we presently do). > > For my example, I'd use this to add random positive increments (specifically > to avoid easy predictability of the sequence), but it would be quite flexible. > > i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10); > > Could do the same for the START, MINVALUE and MAXVALUE parameters also. > > Would others also benefit from this alteration? If so, we might like to ask > for it to be added to Bruce's TODO list. Sorry but my initial impression is that this functionality is pretty strange. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Tom Lane
Date:
Justin Clift <aa2@bigpond.net.au> writes:
> i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10);
Hmm. Bear in mind that the function would have to be executed whilst
holding the lock on the sequence object. There are severe limits on
how long you'd want it to run, and on what it might try to do.
Perhaps we should talk about concrete examples of what would be useful;
I doubt that "execute an arbitrary piece of code" is likely to pass.
> Could do the same for the START, MINVALUE and MAXVALUE parameters also.
What would it mean to change the START value after the sequence has
begun to run? If I reduce the MAXVALUE to something less than the
current sequence value, what happens? Ditto MINVALUE. This concept
needs work.
regards, tom lane
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Shaun Thomas
Date:
On Fri, 22 Jun 2001, Justin Clift wrote: > For my example, I'd use this to add random positive increments (specifically > to avoid easy predictability of the sequence), but it would be quite flexible. It's called a trigger. Just take out the sequence, and build a trigger that does this for you. That's why Postgres has them, after all. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Matt Beauregard
Date:
On Fri, Jun 22, 2001 at 12:43:32PM +1000, Justin Clift wrote: > For my example, I'd use this to add random positive increments (specifically > to avoid easy predictability of the sequence), but it would be quite flexible. > > Would others also benefit from this alteration? If so, we might like to ask > for it to be added to Bruce's TODO list. We would like a way to increment sequences by a random amount, to add some spice to order number generation and such. However it's never made it up the priorities list high enough for us to work out how it might be done. As someone suggested, a trigger might take care of it. -- Matt Beauregard Technical Director, Designscape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633 Permission is given for unlimited distribution of this work under the Copyright Act 1968 (2001 amended). Unless otherwise stated, you agree that these terms also cover works which you send in response.
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Justin Clift
Date:
Hi Matt,
I'm looking for a way to change an existing sequence's "increment" value on
the fly (after it's being created).
Can't seem to find a function which does this either. Being able to change
the increment every now and again would provide useful in some scenario's.
i.e. select setseqinc('foo_idnum_seq', 4);
This would change the sequence foo_idnum_seq to now increment by 4 instead of
whatever it was previously.
Does anyone have any idea of how to achieve this without dropping the
sequence?
Regards and best wishes,
Justin Clift
On Saturday 23 June 2001 03:45, Matt Beauregard wrote:
> On Fri, Jun 22, 2001 at 12:43:32PM +1000, Justin Clift wrote:
> > For my example, I'd use this to add random positive increments
> > (specifically to avoid easy predictability of the sequence), but it would
> > be quite flexible.
> >
> > Would others also benefit from this alteration? If so, we might like to
> > ask for it to be added to Bruce's TODO list.
>
> We would like a way to increment sequences by a random amount, to add
> some spice to order number generation and such. However it's never
> made it up the priorities list high enough for us to work out how it
> might be done. As someone suggested, a trigger might take care of it.
Re: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer
From
Shaun Thomas
Date:
On Wed, 27 Jun 2001, Justin Clift wrote: > Hi Matt, > > I'm looking for a way to change an existing sequence's "increment" value on > the fly (after it's being created). > > Can't seem to find a function which does this either. Being able to change > the increment every now and again would provide useful in some scenario's. Unfortunately, there is no "alter sequence" syntax in Postgres as of yet. You could emulate this by having a trigger address a config table of some sort where you have a column defining the increment size. Then all you'd have to do is change the increment size. Sadly, that would give you a performance hit on inserts. Postgres has a ton of missing "alter" syntax, you just have to learn to live with it. I'm not all that happy with it, either. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+