尝试让IF NOT IN在PostgreSQL中工作

vi4fp9gy 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(123)

我想运行以下函数，将其插入名为User Device的表中
这如下

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
$$;

字符串
在这个函数中，我想要以下内容：
1.检查dbo.User表中是否存在userID，如果不存在则返回1。
1.检查表dbo.device中是否存在DeviceID，如果不存在则返回2。
1.如果1）和2）不为真，则将记录插入到具有DeviceID和UserID值的表UserDevice中。
但是，当我尝试执行这个函数时，我得到以下错误：

[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;

型
我做错什么了？

postgresql

来源：https://stackoverflow.com/questions/77389693/trying-to-get-if-not-in-to-work-in-postgres-sql

1条答案

按热度按时间

osh3o9ms1#

很高兴看到你的工作，并与一对夫妇的提示，想通了-对你有好处。说我提出了一对夫妇的想法和替代解决方案。发展思路：

让数据库做尽可能多的工作。
写尽可能少的代码。
相信代码在大多数情况下都是正确的。
当上述操作失败时，处理异常。

在这里，你定义了FK来强制执行适当的“父”行存在。这样做之后，Postgres将验证它们是否存在，不管你是否这样做。所以你不需要做这个验证，只要让数据库管理器来做就行了。如果“父”行中的任何一个缺失，Postgres会抛出一个异常，然后你可以处理它。假设你需要一个函数来处理这个异常并提供一个结果，我们来：（参见demo here）。

create or replace function spSetDeviceUser(p_deviceid uuid, p_userid uuid) 
        returns integer
       language plpgsql
as $$
declare 
--- return code values 
    k_success        constant integer  = 0;
    k_invalid_user   constant integer  = 1; 
    k_invalid_device constant integer  = 2;
    k_already_exists constant integer  = 3;

begin
/*********************************
* associate a device with a user *
* include columns as necessary.  *
*********************************/
     insert into  userdevice (userid, deviceid)
          values (  p_userid, p_deviceid );
      
     return k_success; 
 
 exception 
    when foreign_key_violation then 
    /*********************************
    * either the device id or the user id does not exist, 
    * find out which and return appropriate error.
    *********************************/
         if exists (select null 
                      from users 
                     where userid =  p_userid
                   ) 
         then 
             return k_invalid_device;
         else 
             return k_invalid_user;
         end if;
    when unique_violation then 
        return k_already_exists ;
    
end; 
$$;

字符串
显然，上面的代码假设生成的是PK值，所以不是insert中的一列。还使用了参数名称而不是位置符号;经历了命名它们的麻烦，不妨使用名称。
此解决方案最多需要一个DML和一个选择。此外，绝大多数情况下只需要一个DML。验证“父”行的解决方案需要2个选择，并且在大多数情况下需要一个DML语句。更少的i/o应该更快。
演示说明：为了简单起见，我将uuid改为integer，并删除了除必要列之外的所有列。

赞(0）回复(0）举报 2023-11-18

我来回答

尝试让IF NOT IN在PostgreSQL中工作

1条答案

相关问题

热门标签

最新问答