sql - 如何在 Postgres 中使用 upsert

Question

我想将 Postgres 中的这段代码转换成更短的代码来完成同样的工作。我阅读了有关 upsert 的内容，但我无法理解在我的代码中实现它的好方法。我写的很好，但我想找到一种更优雅的方式来编写它。希望这里有人可以帮助我!这是查询:

CREATE OR REPLACE FUNCTION insert_table(
    in_guid    character varying,
    in_x_value character varying,
    in_y_value character varying
)
RETURNS TABLE(response boolean) LANGUAGE 'plpgsql'

DECLARE _id integer;

BEGIN
    -- guid exists and it's been 10 minutes from created_date:
    IF ((SELECT COUNT (*) FROM public.tbl_client_location WHERE guid = in_guid AND created_date < NOW() - INTERVAL '10 MINUTE') > 0) THEN
        RETURN QUERY (SELECT FALSE);
    
    -- guid exists but 10 minutes hasen't passed yet:
    ELSEIF ((SELECT COUNT (*) FROM public.tbl_client_location WHERE guid = in_guid) > 0) THEN
    
        UPDATE
            public.tbl_client_location
        SET
            x_value = in_x_value,
            y_value = in_y_value,
            updated_date = now()
        WHERE
            guid = in_guid;

        RETURN QUERY (SELECT TRUE);
    
    -- guid not exist:
    ELSE
       
        INSERT INTO public.tbl_client_location
            ( guid   , x_value   , y_value    )
        VALUES
            ( in_guid, in_x_value, in_y_value )
        RETURNING id INTO _id;  

        RETURN QUERY (SELECT TRUE);

    END IF;
END

Answer 1

这确实可以简单很多:

CREATE OR REPLACE FUNCTION insert_table(in_guid text
                                      , in_x_value text
                                      , in_y_value text
                                      , OUT response bool)  -- ④
  -- RETURNS record  -- optional noise  -- ④
  LANGUAGE plpgsql AS  -- ①
$func$  -- ②
-- DECLARE
   -- _id integer;  -- what for?
BEGIN
   INSERT INTO tbl AS t
          (   guid,    x_value,    y_value)
   VALUES (in_guid, in_x_value, in_y_value)
   ON CONFLICT (guid) DO UPDATE  -- guid exists
   SET    (         x_value,          y_value, updated_date)
        = (EXCLUDED.x_value, EXCLUDED.y_value, now())  -- ⑤
   WHERE  t.created_date >= now() - interval '10 minutes'  -- ③ have not passed yet
   -- RETURNING id INTO _id  -- what for?
   ;
   response := FOUND;  -- ⑥
END
$func$;

假设 guid 被定义为 UNIQUE 或 PRIMARY KEY，并且 created_date 被定义为 NOT NULL DEFAULT现在()。

① 语言名称是一个标识符 - 最好不带引号。

② 函数体周围的引号丢失(无效命令)。见:

• What are '$$' used for in PL/pgSQL

③ UPDATE 仅当 10 分钟还未过去时。请记住，时间戳是从各个交易开始的时间戳。因此，请保持交易简短。见:

• Difference between now() and current_timestamp

④ 带有OUT 参数且没有RETURNS 子句的函数自动返回单行(record)。您的原件被声明为设置返回函数(0-n 返回的行)，这没有意义。见:

• Return multiple fields as a record in PostgreSQL with PL/pgSQL

⑤ 使用特殊的 EXCLUDED 行通常比再次拼写值更好。见:

• How could this UPSERT query be made shorter?

⑤ 也使用短语法来更新多列。见:

• Update multiple columns that start with a specific string

⑥ 要查看一行是否已写入，请使用特殊变量 FOUND .细微差别:与您原来的不同，您得到 true 或 false after the fact，表示一行实际上已被写入(或未写入) .在您的原始版本中，INSERT 或 UPDATE 可能仍会被触发器或规则跳过(不引发异常)，并且在这种情况下函数结果会产生误导。见:

• IS NOT NULL test for a record does not return TRUE when variable is set

进一步阅读:

• Postgres ON CONFLICT ON CONSTRAINT triggering errors in the error log
• How to use RETURNING with ON CONFLICT in PostgreSQL?

---

您可能只运行单个 SQL 语句，一次提供您的值:

INSERT INTO tbl AS t(guid, x_value,y_value)
VALUES ($in_guid, $in_x_value, $in_y_value)  -- your values here, once
ON CONFLICT (guid) DO UPDATE
SET    (x_value,y_value, updated_date)
     = (EXCLUDED.x_value, EXCLUDED.y_value, now())
WHERE  t.created_date >= now() - interval '10 minutes';