Apologies if this appears twice - testing a new email client. There was some discussion the other day about a desire for int8 sequences. This simulates that by providing a large base value combined with an int4 sequence. You will need to reset the big sequence to a new base value every once in a while. The one thing to be careful of is resetting a big sequence while another process is using pgx_curr_big_val() - the base value will be changed anyway. You can protect against this by issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE within a transaction but that will stop pgx_next_big_val() from incrementing. You have been warned. BTW - where should I post stuff for contrib directory, and is this suitable? - Richard Huxton -- Big sequence generator: generates int8 sequences from a base value -- and int4 sequence -- -- Requirements: Postgres v7.1 with plpgsql enabled on your database -- -- Usage: -- select pgx_create_big_seq('testseq',1000000); -- select pgx_next_big_val('testseq'); -- select pgx_curr_big_val('testseq'); -- -- At intervals you will need to reset the big sequence to a new base -- select pgx_reset_big_seq('testseq',2000000); -- -- This code is provided as-is and licenced under the same terms as PostgreSQL -- -- Author: Richard Huxton -- Email: [EMAIL PROTECTED] -- Version: 1.0 (2001-03-07) -- -- Create a table to track our int8 sequences -- CREATE TABLE pgx_bigseq ( bigname varchar(64) not null, base int8, last_reset timestamp ); CREATE UNIQUE INDEX pgx_bigseq_name ON pgx_bigseq(bigname); -- -- pgx_create_big_seq(BIG-SEQ-NAME, BASE) -- CREATE FUNCTION pgx_create_big_seq(text, int8) RETURNS text AS ' DECLARE bigseqname ALIAS FOR $1; bigbase ALIAS FOR $2; seqname text; BEGIN seqname := ''pgx_'' || bigseqname; EXECUTE ''CREATE SEQUENCE '' || seqname; INSERT INTO pgx_bigseq VALUES (bigseqname, bigbase, now()); RETURN ''Big sequence '' || bigseqname || '' created.''; END; ' language 'plpgsql'; -- -- pgx_next_big_val(SEQ) -- CREATE FUNCTION pgx_next_big_val(text) RETURNS int8 AS ' DECLARE bigseq ALIAS FOR $1; nextbigval int8; BEGIN SELECT INTO nextbigval base FROM pgx_bigseq WHERE bigname = bigseq; IF NOT FOUND THEN RAISE EXCEPTION ''Unknown big sequence %'', bigseq; END IF; nextbigval := nextbigval + nextval(''pgx_'' || bigseq); RETURN nextbigval; END; ' language 'plpgsql'; -- -- pgx_curr_big_val(SEQ) -- CREATE FUNCTION pgx_curr_big_val(text) RETURNS int8 AS ' DECLARE bigseq ALIAS FOR $1; nextbigval int8; BEGIN SELECT INTO nextbigval base FROM pgx_bigseq WHERE bigname = bigseq; IF NOT FOUND THEN RAISE EXCEPTION ''Unknown big sequence %'', bigseq; END IF; nextbigval := nextbigval + currval(''pgx_'' || bigseq); RETURN nextbigval; END; ' language 'plpgsql'; -- -- pgx_reset_big_seq(NEWBASE) -- CREATE FUNCTION pgx_reset_big_seq(text, int8) RETURNS text AS ' DECLARE bigseqname ALIAS FOR $1; newbase ALIAS FOR $2; seqname text; BEGIN seqname := ''pgx_'' || bigseqname; EXECUTE ''SELECT setval('''''' || seqname || '''''',1,true)''; UPDATE pgx_bigseq SET base=newbase, last_reset=now() WHERE bigname=bigseqname; RETURN ''Big sequence '' || bigseqname || '' reset.''; END; ' language 'plpgsql'; ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html