如果条目已存在于具有 UNIQUE 约束的表中，则插入或返回行

发布于 2024-09-12 20:19:09 字数 836 浏览 8 评论 0原文

您好，

我在 postgreSQL 8.4 服务器上有这张表：

CREATE TABLE tags (
 tagid        bigserial                 PRIMARY KEY,
 name         text                      NOT NULL,
 value        text                      NOT NULL,
 UNIQUE(name,value)
);

正常的 INSERT 行为是在新值打破唯一性约束时抛出错误。我希望它不要抛出错误并返回新的 tagid（如果插入成功），或者返回与唯一性约束匹配的现有条目的 tagid。

我使用这个函数来做到这一点：

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $$
DECLARE
 retval bigint;
BEGIN
 SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
 IF FOUND THEN 
  RETURN retval; 
 END IF;
 INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
 RETURN retval;
END;
$$ LANGUAGE plpgsql;

在最坏的情况下，在插入之前完成两次表查找。有没有更好的方法来做到这一点，可能在一次查找中？

原文

Greetings,

I have this table, on a postgreSQL 8.4 server :

CREATE TABLE tags (
 tagid        bigserial                 PRIMARY KEY,
 name         text                      NOT NULL,
 value        text                      NOT NULL,
 UNIQUE(name,value)
);

The normal INSERT behavior is to throw an error when new values break the uniqueness constraint. I would prefer for it not to throw the error and return either the new tagid if then insertion succeeded, or the tagid of the existing entry matching the uniqueness constraint.

I use this function to do this :

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $
DECLARE
 retval bigint;
BEGIN
 SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
 IF FOUND THEN 
  RETURN retval; 
 END IF;
 INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
 RETURN retval;
END;
$ LANGUAGE plpgsql;

In the worst case two table lookups are done before insertion. Is there a better way to do it, possibly in one lookup ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女的英雄梦 2024-09-19 20:19:09

只需 INSERT 并进行一些异常处理：

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $
DECLARE
 retval bigint;
BEGIN
    INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
    RETURN retval;

    EXCEPTION
        WHEN unique_violation THEN
            SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
            RETURN retval;
END;
$ LANGUAGE plpgsql;

您可以在手册中找到有关此内容的更多信息：
http://www.postgresql。 org/docs/current/static/plpgsql-control-structs.html#PLPGSQL-ERROR-TRAPPING

Just INSERT and do some exception handling:

CREATE OR REPLACE FUNCTION insert_tags(my_name text, my_value text)
RETURNS bigint AS $
DECLARE
 retval bigint;
BEGIN
    INSERT INTO tags (name, value) VALUES (my_name, my_value) RETURNING tagid INTO retval;
    RETURN retval;

    EXCEPTION
        WHEN unique_violation THEN
            SELECT tagid INTO retval FROM tags WHERE name = my_name AND value = my_value;
            RETURN retval;
END;
$ LANGUAGE plpgsql;

You can find more information about this in the manual:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

回复收藏 0 原文

~没有更多了~