lundi 6 avril 2015

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;




Aucun commentaire:

Enregistrer un commentaire