PostgreSQL中IF NOT IN语法错误排查：用户设备关联函数问题

问题背景 #

需要编写PLPGSQL函数spSetDeviceUser，实现以下功能：

• 检查传入的UserID是否存在于dbo."user"表，不存在则返回1；
• 检查传入的DeviceID是否存在于dbo.device表，不存在则返回2；
• 上述检查通过后，向dbo.userdevice表插入一条用户与设备的关联记录。

用户编写的函数代码如下：

create function spSetDeviceUser(DeviceID uuid, UserID uuid) returns INT
    language PLPGSQL
as
$$

/********************
* Check user exists *
********************/
BEGIN
     IF  $2 NOT IN (SELECT UserID  FROM dbo.user) THEN
             RETURN 1 -- User doesn't exist
     END IF;

/**********************
* Check device exists *
**********************/

     IF  $1 NOT IN (SELECT DeviceID  FROM dbo.Device) THEN
             RETURN 2 -- User doesn't exist
     END IF;


/*********************************
* Associate a device with a user *
*********************************/


INSERT INTO dbo.UserDevice (UserDeviceID, UserID, DeviceID)
VALUES (uuid_generate_v4(), $2, $1);

END
$$;

执行该函数时出现以下错误：

[2023-10-30 15:11:28] [42601] ERROR: syntax error at or near "IF"
[2023-10-30 15:11:28] Position: 292

相关表结构定义：

-- auto-generated definition
create table "user"
(
    userid         uuid not null
        constraint pk__user__1788ccac19748a10
            primary key
        constraint uq__user__1788ccad4f14aa65
            unique,
    username       varchar(100)
        constraint uq__user__c9f2845657920267
            unique,
    passwordhash   bytea,
    firstname      varchar(40),
    lastname       varchar(40),
    salt           uuid,
    organisationid uuid
        constraint fk_user_organisation_organisationid
            references organisation,
    sessiontoken   uuid,
    sessionage     timestamp with time zone
);

alter table "user"
    owner to postgres;

-- auto-generated definition
create table device
(
    deviceid              uuid not null
        constraint pk__device__49e123311461d246
            primary key,
    serialnumber          varchar(255),
    productcode           varchar(255),
    description           varchar(255),
    softwareversion       varchar(255),
    build                 varchar(255),
    builddate             timestamp with time zone,
    assigned              boolean,
    groupid               uuid
        constraint fkdevice951246
            references groups,
    updateddatetime       timestamp with time zone,
    restartpointerno      integer,
    deviceconnectionindex integer
        constraint fkdevice_deviceconnectiontype
            references deviceconnectiontype,
    organisationid        uuid
        constraint fk_device_organisation_organisationid
            references organisation
);

alter table device
    owner to postgres;

-- auto-generated definition
create table userdevice
(
    userdeviceid uuid not null
        constraint pk__userdevi__fd151c39ccac5a1d
            primary key,
    userid       uuid
        constraint fkuser_userdevice_userid
            references "user",
    deviceid     uuid
        constraint fkuser_userdevice_deviceid
            references device,
    constraint unique_userid_deviceid
        unique (userid, deviceid)
);

alter table userdevice
    owner to postgres;

错误原因分析 #

1. 语法缺失分号：PLPGSQL要求每条语句末尾必须加分号，你的RETURN 1和RETURN 2语句后都没有分号，这是触发syntax error at or near "IF"的直接原因。
2. 注释错误：第二个检查的注释写的是-- User doesn't exist，实际应该是设备不存在，属于逻辑注释错误，不影响语法但需要修正。
3. 位置参数可读性差：使用$1、$2位置参数不如直接使用函数参数名（DeviceID、UserID）直观，不利于代码维护。
4. 存在性检查可靠性不足：NOT IN子查询如果返回NULL值，会导致整个条件结果为未知，无法正确判断记录是否存在，建议改用NOT EXISTS更可靠。
5. 未处理唯一约束冲突：userdevice表有unique_userid_deviceid唯一约束，若重复插入同一用户与设备的关联记录会报错，建议添加冲突处理逻辑。

修复后的函数代码 #

create function spSetDeviceUser(p_DeviceID uuid, p_UserID uuid) returns INT
    language PLPGSQL
as
$$
BEGIN
    -- 检查用户是否存在
    IF NOT EXISTS (SELECT 1 FROM dbo."user" WHERE userid = p_UserID) THEN
        RETURN 1; -- 用户不存在
    END IF;

    -- 检查设备是否存在
    IF NOT EXISTS (SELECT 1 FROM dbo.device WHERE deviceid = p_DeviceID) THEN
        RETURN 2; -- 设备不存在
    END IF;

    -- 插入用户设备关联记录，处理唯一约束冲突
    INSERT INTO dbo.userdevice (userdeviceid, userid, deviceid)
    VALUES (uuid_generate_v4(), p_UserID, p_DeviceID)
    ON CONFLICT (userid, deviceid) DO NOTHING; -- 若已存在则忽略，也可改为RETURN 3表示冲突

    RETURN 0; -- 操作成功返回0
END
$$;

修复说明 #

• 给RETURN语句添加了分号，解决语法错误；
• 改用NOT EXISTS替代NOT IN，避免NULL值导致的判断异常；
• 使用带前缀的参数名p_DeviceID、p_UserID，避免与表字段名冲突，提升可读性；
• 添加ON CONFLICT子句处理唯一约束冲突，避免插入重复记录报错；
• 添加操作成功的返回值0，让函数返回值更清晰。

内容的提问来源于stack exchange，提问作者ChrisAsi71