Hey I want to create a random number. When this random number already exist i want to call the
randomnumber function again and again. In that case i need to return the
randomnumber function inside in CASE WHEN statement.
It does not work. Still get error that number already exist. I want to create a random number for unique column:
CREATE OR REPLACE FUNCTION getrandomnumber(integer, integer) RETURNS integer AS $BODY$ DECLARE start_int ALIAS FOR $1; end_int ALIAS FOR $2; name int; BEGIN name = trunc(random() * (end_int-start_int) + start_int); CASE WHEN (select count(alias) from drivers where alias = name limit 1) = 0 THEN RETURN name; ELSE RETURN getrandomnumber(start_int, end_int); END CASE; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100; ALTER FUNCTION getrandomnumber(integer, integer) OWNER TO postgres;Answer1:
Your function works correctly for me when run without concurrency, i.e. by one user at a time.
CREATE TABLE drivers (alias integer); INSERT INTO drivers(alias) VALUES (1),(2); CREATE OR REPLACE FUNCTION ...;
INSERT INTO drivers(alias) VALUES (getrandomnumber(1, 5));
works twice, then fails with infinite recursion.<hr />
Your function will not work correctly if it is called at the same time from multiple sessions. You must
LOCK TABLE drivers IN EXCLUSIVE MODE or be prepared to handle unique violation errors.
I think what you really want is something more like a "random sequence" that doesn't repeat, e.g. <a href="https://wiki.postgresql.org/wiki/Pseudo_encrypt" rel="nofollow">the pseudo-encrypt function</a>.<hr />
(select count(alias) from drivers where alias = name limit 1) = 0
will work, you should probably try:
exists (select 1 from drivers where alias = name limit 1)
as it's generally faster.