Saturday, December 22, 2007

gapless sequence without using extra table, however, locks the whole table

CREATE OR REPLACE FUNCTION get_tryrecord_lastid()
RETURNS int AS
$$
begin
lock table tryrecord;
return coalesce( (select tryrecord_id from tryrecord order by tryrecord_id desc limit 1) + 1, 1 );
end;
$$
LANGUAGE 'plpgsql' ;





CREATE TABLE tryrecord
(
tryrecord_id integer NOT NULL DEFAULT get_tryrecord_lastid(),
name character varying(20) NOT NULL,
CONSTRAINT pk_tryrecord PRIMARY KEY (tryrecord_id)
);



begin;
insert into tryrecord(name) values('see');
insert into tryrecord(name) values('great');
end;

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home