sequential invoice numbers?

From: will trillich <will(at)serensoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: sequential invoice numbers?
Date: 2002-01-10 17:22:55
Message-ID: 20020110112255.B27772@serensoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

the serial datatype guarantees a unique id on every invocation
(nextval('table_field_seq')) but also warns there can be gaps in
the numbering sequence (after a begin/rollback for example).

is there a facility for guaranteeing sequential numbers, as for
invoicing? our accountant-type anal retentives seem to not like
gaps in the numbering. what's the elegant way to arrange that?

i'm thinking about something like this--

CREATE FUNCTION this_id() RETURNS INTEGER AS '
SELECT MAX(id) FROM sometable
' LANGUAGE 'sql';

CREATE FUNCTION next_id() RETURNS INTEGER AS '
DECLARE
new_id INTEGER;
BEGIN
BEGIN;
LOCK TABLE sometable EXCLUSIVE;
new_id := 1 + this_id();
INSERT INTO sometable(id) VALUES (new_id);
COMMIT;
RETURN new_id;
END;
' LANGUAGE 'plpgsql';

CREATE VIEW someview AS
SELECT * FROM sometable;

CREATE RULE add_id AS
ON INSERT TO someview
DO INSTEAD (
UPDATE sometable SET
f1 = NEW.f1,
f2 = NEW.f2,
fN = NEW.fN
WHERE
id = next_id() -- does the lock mess up the update?
;
);

is this the paradigm to use with v7.1? maybe the lock belongs in
the rule, instead? or am i all wet?

--
DEBIAN NEWBIE TIP #100 from Leonard Stiles <ljs(at)uk2(dot)net>
:
Looking for a way to CREATE A PAGE OF LINKS to all the
*/index.html that already exist in your /usr/share/doc tree?
#!/bin/sh
# as /usr/doc contains lots of symlinks to /usr/share/doc, adding
# -follow to find args may be useful.
{ echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"'
echo '"http://www.w3.org/TR/html40/strict.dtd">';
echo '<html><head><title>Documentation Links</title></head><body>';
find /usr/doc -name index.html \
-printf '<href="file:%p">%P</a><br>\n';
echo '</body></html>';
} > links.html

Also see http://newbieDoc.sourceForge.net/ ...

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2002-01-10 17:34:37 Re: Performance tips
Previous Message Arguile 2002-01-10 17:06:07 Re: duplicating table