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@uk2.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/ ...
