I'm quite new to Postgres, and I've been trying to create a DB function which would produce a positive seeded integer in a given range based on a string seed.
I've actually managed to make it work, however, I don't believe my solution is really all that great and would appreciate, if you could suggest a better solution, provided there is one.
So far I have this:
CREATE OR REPLACE FUNCTION seeded_int(p_low integer, p_high integer, p_seed text)
RETURNS integer
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_num integer;
BEGIN
SELECT ('x' || SUBSTR(MD5(p_seed), 1, 8))::bit(32)::int INTO v_num;
PERFORM SETSEED(v_num / 2147483647.0); -- 2^31 - 1
RETURN FLOOR(RANDOM() * (p_high - p_low + 1) + p_low);
END;
$BODY$;
As you can see, my function consists of three steps:
- Generating integer from seed by using MD5 hash function (borrowed from this SO answer)
- Dividing the integer so that it fits between -1 and 1 and could be used as seed (value taken from the documentation of SEED parameter)
- Generating seeded integer in a given range
The problem:
My biggest problem is with the second step where I literally divide the number v_num
, which I want to use as seed, by the exact same value 2^31-1
which would then be used to multiply the seed again, as described in the documentation:
SEED
Sets the internal seed for the random number generator (the function random). Allowed values are floating-point numbers between -1 and 1, which are then multiplied by 2^31-1.
Is it possible to set the seed without redundant operations?
Is there a better way how to approach generating seeded integers?
EDIT: I just realized I didn't actually need SETSEED() and RANDOM() functions at all. The integer generated in the 1st step is already seeded, and all I have to do is recalculate it so that it fits the given range. I believe that, in order to do so, I just need to fit the value between 0 and 1 and use it instead of RANDOM() in the 3rd step. I'm kind of disappointed in myself for not seeing this immediately...
Aucun commentaire:
Enregistrer un commentaire