Thread: sequence help

I'll jump right in with an example

create sequence foo start with 1;

create view foobar as select *, nextval('foo') from bar;

The problem is I need a nextval()-like method that returns the iterations without
updating the foo sequence. Therefore, maintaining the sequences original value by
starting at 1 each time the foobar VIEW is invoked.

This is obviously a simplified example for an larger query dependent on row order
integrity in a view.

On http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html I did not
find any way to avoid updating sequences (albeit they are supposed to update by
design and may need to update in order to pull the next in sequence). Nor was I able
to maintain iteration in a nextval()-like function. Any ideas?

Matt



____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

Matthew Peter wrote:
> I'll jump right in with an example
>
> create sequence foo start with 1;
>
> create view foobar as select *, nextval('foo') from bar;
>
> The problem is I need a nextval()-like method that returns the iterations without
> updating the foo sequence. Therefore, maintaining the sequences original value by
> starting at 1 each time the foobar VIEW is invoked.

And what would you expect to see if more than one person simultaneously
queries foobar? A sequence would increment for each session querying it.

I think you want numbers to increase per session, in which case you
could probably use generate_series().

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On þri, 2006-11-28 at 01:12 -0800, Matthew Peter wrote:
> I'll jump right in with an example
>
> create sequence foo start with 1;
>
> create view foobar as select *, nextval('foo') from bar;
>
> The problem is I need a nextval()-like method that returns the iterations without
> updating the foo sequence. Therefore, maintaining the sequences original value by
> starting at 1 each time the foobar VIEW is invoked.

sounds like you do not really want sequences. (think of
concurrent selects from such a view)

the most natural way to do this is in the application
layer, but if you have to do this in the backend, you
could do it with a pl/pgsql user defined set returning
function, I guess.

gnari