PostgreSQL插入数据的函数返回布尔值时报错的问题咨询

问题场景 #

现有community模式下的member表，需创建函数add_member：当传入的邮箱不存在于表中时，插入该邮箱并返回布尔值TRUE，否则返回FALSE。编写的函数代码如下：

CREATE OR REPLACE FUNCTION
  add_member(member_email TEXT)
RETURNS BOOLEAN AS
  $$
    BEGIN
        IF NOT EXISTS (SELECT email FROM "community".member WHERE email = $1) THEN
            INSERT INTO "community".member (email) VALUES ($1) RETURNING TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;
  $$
LANGUAGE PLPGSQL;

SELECT add_member('cheese.head@green.org');

执行时触发错误：

ERROR:  query has no destination for result data

错误原因 #

问题确实出在INSERT ... RETURNING的使用方式上。在PLPGSQL中，带有RETURNING子句的INSERT语句返回的结果必须有明确的接收目标——要么存入变量，要么通过RETURN语句直接返回。原代码里的INSERT INTO ... RETURNING TRUE没有处理返回结果，导致PLPGSQL无法识别该结果的去向，从而抛出错误。

修正方案 #

方案一：用变量接收返回结果 #

声明变量接收RETURNING的结果，再通过RETURN语句返回：

CREATE OR REPLACE FUNCTION
  add_member(member_email TEXT)
RETURNS BOOLEAN AS
  $$
    DECLARE
      insert_result BOOLEAN;
    BEGIN
        IF NOT EXISTS (SELECT email FROM "community".member WHERE email = $1) THEN
            -- 将RETURNING的结果存入变量
            INSERT INTO "community".member (email) VALUES ($1) RETURNING TRUE INTO insert_result;
            RETURN insert_result;
        ELSE
            RETURN FALSE;
        END IF;
    END;
  $$
LANGUAGE PLPGSQL;

方案二：使用ON CONFLICT避免竞态（推荐） #

如果member表的email字段已设置唯一约束，可以用ON CONFLICT语法替代先查询再插入的逻辑，既简化代码，又能避免并发场景下的竞态问题（比如两个请求同时插入同一个邮箱时，先查后插可能导致重复插入）：

CREATE OR REPLACE FUNCTION
  add_member(member_email TEXT)
RETURNS BOOLEAN AS
  $$
    BEGIN
        -- 尝试插入，若邮箱已存在则不做任何操作
        INSERT INTO "community".member (email) VALUES ($1)
        ON CONFLICT (email) DO NOTHING
        RETURNING TRUE;
        
        -- 通过FOUND变量判断是否成功插入
        RETURN FOUND;
    END;
  $$
LANGUAGE PLPGSQL;

FOUND是PLPGSQL的内置变量，当最近的SQL语句（这里是INSERT）影响了至少一行数据时，FOUND会被设为TRUE，否则为FALSE，刚好符合需求。

内容的提问来源于stack exchange，提问作者M.Holmes