Postgres - 在语句情况下返回函数 [英] Postgres - Return function in Case When Statement

问题描述

嘿，我想创建一个随机数。当这个随机数已经存在时，我想一次又一次地调用 randomnumber 函数。在这种情况下，我需要返回CASE WHEN语句中的 randomnumber 函数。
它不起作用。仍然会出现该号码已经存在的错误。我想为独立的列创建一个随机数：

$ p $ $ codeREATE OR REPLACE FUNCTION getrandomnumber
RETURNS整数AS
$ BODY $
DECLARE
start_int别名为$ 1;
end_int别名$ 2;
name int;
BEGIN
name = trunc（random（）*（end_int-start_int）+ start_int）;

CASE WHEN（select alias = name limit 1）中的select count（别名）= 0

THEN RETURN name;

ELSE RETURN getrandomnumber（start_int，end_int）;

END CASE;
END;

$ BODY $

语言plpgsql挥发严格
成本100;
ALTER FUNCTION getrandomnumber（integer，integer）
OWNER TO postgres;

解决方案

在没有并发运行的情况下，即一次只有一个用户。

与：

  CREATE TABLE驱动程序（别名整数）; 
 
 INSERT INTO驱动程序（别名）VALUES（1），（2）; 
 
创建或替换功能...; 
  

然后

  INSERT INTO驱动程序（别名）VALUES（getrandomnumber（1，5））; 
  

工作两次，然后无限递归失败。

---

如果您的功能在多个会话中同时被调用，将无法正常工作。您必须 LOCK TABLE驱动程序在独占模式下或准备处理唯一的违规错误。 想要的东西更像是一个不重复的随机序列，例如伪加密功能。

---

顺便说一下，

 （select alias =名称限制1）= 0 
  

会起作用，您应该试试：

 存在（从alias = name limit 1的驱动程序中选择1）
  

，因为它通常更快。

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;

解决方案

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.

这篇关于Postgres - 在语句情况下返回函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！