Transform text to double between -1.0 and 1.0 for use as a seed [postgresql]

I need to generate a random value using random() based on a seed which comes from an existing table. The seed is a text but setseed() requires a double between -1.0 and 1.0. The reason is that I need the random number to be the same given an existing value in my database.

I want to define a function that takes the seed and returns a random number based on that seed:

CREATE OR REPLACE FUNCTION random_with_seed(seed text) RETURNS double precision AS
    $$
        DECLARE casted double precision;
        BEGIN
            -- cast seed to a double between -1.0 and 1.0
            casted = ???

            setseed(casted);

            RETURN random();
        END
    $$ LANGUAGE plpgsql IMMUTABLE;

​x
 
CREATE OR REPLACE FUNCTION random_with_seed(seed text) RETURNS double precision AS    $$        DECLARE casted double precision;        BEGIN            -- cast seed to a double between -1.0 and 1.0            casted = ???​            setseed(casted);​            RETURN random();        END    $$ LANGUAGE plpgsql IMMUTABLE;​

What would be the best way to transform the text into a valid argument for setseed()?

Answer

SELECT cos(hashtext('some text'));
         cos         
---------------------
 -0.9092973651315766
(1 row)

 
SELECT cos(hashtext('some text'));         cos         --------------------- -0.9092973651315766(1 row)​

Advertisement

Answer