Re: sequence's plpgsql

On Wednesday 24 September 2003 17:40, Tim McAuley wrote:
> Hi,
>
> I've hit a little problem and was wondering if anyone might be able to
> give some help.
>
> Set-up:
>
> - JBoss appserver using entity beans to access database
> - Using sequence pattern to update primary keys for several tables.
> Basically it is a method of getting primary keys without hitting the
> database. It actually hits the database every 10th go and updates the
> counter by 10 in the database.

You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.

Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.

If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;

That any use?
--
Richard Huxton
Archonet Ltd