Postgres - Return function in Case When Statement

问题:

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;

回答1:

Your function works correctly for me when run without concurrency, i.e. by one user at a time.

With:

CREATE TABLE drivers (alias integer);

INSERT INTO drivers(alias) VALUES (1),(2);

CREATE OR REPLACE FUNCTION ...;

then

INSERT INTO drivers(alias) VALUES (getrandomnumber(1, 5));

works twice, then fails with infinite recursion.

---

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. the pseudo-encrypt function.

---

BTW, while:

(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.