Tim Pushor wrote:
> I had thought about this as well - I am developing an invoicing
> application for my small business.
>
> There are definately applications that you want to ensure that there
> are no 'holes' in the numbers, such as invoice numbers - technical
> limitations notwithstanding..
>
> Tim
>
>
<snip>
Off the top of my head the only way I can see to reliably do this is something like the following:
BEGIN;
CREATE FUNCTION nxt_tst_id() RETURNS int4 AS '
SELECT 0 AS RESULT;
' LANGUAGE SQL VOLATILE SECURITY INVOKER;
CREATE TABLE tst (
id int4 DEFAULT nxt_tst_id(),
msg text,
PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION nxt_tst_id() RETURNS int4 AS '
LOCK TABLE tst IN SHARE MODE;
SELECT id+1 as result FROM tst ORDER BY id DESC LIMIT 1;
' LANGUAGE SQL VOLATILE SECURITY INVOKER;
INSERT INTO tst (id,msg) VALUES (0,'seed');
INSERT INTO tst (msg) VALUES ('test');
COMMIT;
However this has thwe disadvantage that the entire table must be locked in share mode to prevent further updates.
hth,
- Stuart
P.S. Sorry if this becomes HTML from the disclaimer adder thing
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.
