postgresql 中的动态 upsert

发布于 2024-09-04 18:58:48 字数 747 浏览 5 评论 0原文

我有这个 upsert 函数，它允许我修改一行的 fill_rate 列。

CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS '
DECLARE
    num ALIAS FOR $1;
    dat ALIAS FOR $2;

BEGIN
    LOOP
        -- First try to update.
    UPDATE alarming SET fill_rate = dat WHERE equipid = num;
    IF FOUND THEN
        RETURN;
    END IF;
    -- Since its not there we try to insert the key
    -- Notice if we had a concurent key insertion we would error
    BEGIN
        INSERT INTO alarming (equipid, fill_rate) VALUES (num, dat);
        RETURN;
    EXCEPTION WHEN unique_violation THEN
        -- Loop and try the update again
    END;
    END LOOP;
END;
' LANGUAGE 'plpgsql';

是否可以修改此函数以也采用列参数？如果有办法修改函数以获取列和表，则会额外加分。

原文

I have this upsert function that allows me to modify the fill_rate column of a row.

CREATE FUNCTION upsert_fillrate_alarming(integer, boolean) RETURNS VOID AS '
DECLARE
    num ALIAS FOR $1;
    dat ALIAS FOR $2;

BEGIN
    LOOP
        -- First try to update.
    UPDATE alarming SET fill_rate = dat WHERE equipid = num;
    IF FOUND THEN
        RETURN;
    END IF;
    -- Since its not there we try to insert the key
    -- Notice if we had a concurent key insertion we would error
    BEGIN
        INSERT INTO alarming (equipid, fill_rate) VALUES (num, dat);
        RETURN;
    EXCEPTION WHEN unique_violation THEN
        -- Loop and try the update again
    END;
    END LOOP;
END;
' LANGUAGE 'plpgsql';

Is it possible to modify this function to take a column argument as well? Extra bonus points if there is a way to modify the function to take a column and a table.

分享到QQ

分享到微博

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

发布评论

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

命硬 2024-09-11 18:58:48

作为一种替代方法，您可以在不使用函数的情况下执行 upsert，方法是使用带有 where 子句的插入 + 更新，使它们仅在正确的情况下成功。例如，

update mytable set col1='value1' where (col2 = 'myId');
insert into mytable select 'value1', 'myId' where not exists (select 1 from mytable where col2='myId');

这将避免拥有大量自定义 postgres 特定功能。

As an alternative approach, you can do an upsert without a function by using an insert + update with where clauses to make them only succeed in the right case. E.g.

update mytable set col1='value1' where (col2 = 'myId');
insert into mytable select 'value1', 'myId' where not exists (select 1 from mytable where col2='myId');

Which would avoid having lots of custom postgres specific functions.

回复收藏 0 原文

心奴独伤 2024-09-11 18:58:48

您想阅读动态命令plsql。
只需构建您的查询并调用 EXECUTE。

回复收藏 0 原文

春夜浅 2024-09-11 18:58:48

也许有一个更简单的方法，只是更少的线；）

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $
DECLARE
BEGIN
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
    IF NOT FOUND THEN
    INSERT INTO tableName values (value, arg1, arg2);
    END IF;
END;
$ LANGUAGE 'plpgsql';

Maybe a simpler approach, just less line ;)

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $
DECLARE
BEGIN
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
    IF NOT FOUND THEN
    INSERT INTO tableName values (value, arg1, arg2);
    END IF;
END;
$ LANGUAGE 'plpgsql';

回复收藏 0 原文

~没有更多了~