CONTRIB: int8 sequence simulator

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: dev(at)archonet(dot)com
-- 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';