I have a problem that is too hard for me, so I've decided to turn to you gurus.
I need random five-digit sequence, with MINVALUE 10000 and MAXVALUE 99999, to pe put in a column with every INSERT. Since regular sequences can't achieve this, can it be done some other way in PostgreSQL? Of course, Something like NO CYCLE feture would be useful. Am I asking too much?
We had a similar need where we wanted to store state information about a web session, but wanted a random key that the browser passed in -- making it much harder for someone to guess a valid value.
The obvious choice would be to set the default value to some random function, but that does not ensure uniqueness. You can't just check to see that it's not in the table either, since it could be inserted by another transaction after you between when you checked for it and when you inserted it. Further, another uncommitted transaction may be trying to insert the same value at the same time, and your transaction would not see it. This opens up a host of race conditions.
In the end we wrote a function that replaces the INSERT command. This function tries to insert the values with different random keys until it succeeds and returns the key.
Here it is. Of course, replace the function, field and table names to suit your liking.
CREATE OR REPLACE FUNCTION
RETURNS damnow_stat.sel%TYPE AS '
-- selector we will return
-- user passed in vars
v_uid ALIAS for $1;
v_var ALIAS for $2;
-- do not recycle selectors -- it helps
-- avoid browser caching problems
-- get next sel value
-- keep looking until we have no collisions
-- use the random function and then mash it up
-- via MD5.
-- note the bizarre use of insert / where
-- this gives us atomicity so that there
-- are no race conditions. This will either
-- insert a record or fail to, without raising
-- a Pg exception. Later on, we check the FOUND
-- variable to determine if an insert actually happend
SELECT INTO r_sel MD5(RANDOM());
-- insert it, but ONLY if not already there
INSERT INTO damnow_stat (sel, user_id, vars)
SELECT r_sel, v_uid, v_var
WHERE NOT EXISTS (
WHERE sel = r_sel
-- exit if this actually worked
EXIT WHEN FOUND;
' LANGUAGE plpgsql SECURITY DEFINER;