mardi 10 mai 2022

How to generate integers based on a string seed

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:

  1. Generating integer from seed by using MD5 hash function (borrowed from this SO answer)
  2. 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)
  3. 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