sql - 插入 COALESCE(NULL,default)

Question

我有使用 UUID 的表。我希望能够插入带有或不带有 UUID 的新行，因为有时客户端会生成 UUID，但有时不会。

每个表的核心都是这个:

CREATE TABLE IF NOT EXISTS person (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

我正在尝试使用一个函数来插入行。我希望能够传递一个 NULL id 并获得一个默认值(生成的 UUID)。我有这样的东西:

CREATE OR REPLACE FUNCTION create_person(
    id UUID
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    INSERT INTO person( id )
    VALUES (
        COALESCE(id,default)
    );
    RETURN FOUND;
END;
$$;

我已经试过了:

INSERT INTO person ( id ) VALUES (
    COALESCE(id, default),
);

还有这个:

INSERT INTO person ( id ) VALUES (
  CASE WHEN id IS NULL THEN default ELSE id END
);

这有效，但它重复了 gen_random_uuid() 代码:

INSERT INTO person ( id ) VALUES (
  COALESCE(id, gen_random_uuid()),
);

同样这也有效，但有同样的问题:

INSERT INTO person ( id ) VALUES (
    CASE WHEN id IS NULL THEN gen_random_uuid() ELSE id END
);

有没有一种方法可以做到这一点而不必重复 gen_random_uuid() 代码？

使用 plpgsql 会更好吗？

Answer 1

核心问题是附加到 INSERT 的 VALUES 表达式中关键字 DEFAULT 的特殊性质。 Per documentation :

In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column's default value should be inserted. DEFAULT cannot be used when VALUES appears in other contexts.

大胆强调我的。具体来说，DEFAULT 不能作为函数的参数:

<strike>COALESCE(<i>function_parameter</i>, DEFAULT)</strike>  -- not possible

可能的解决方案

有多种方法，具体取决于确切的要求。

此函数不需要知道 person.id 的实际默认值 - 这似乎是您所追求的:

CREATE OR REPLACE FUNCTION create_person(_id UUID)
  RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS
$func$
BEGIN
   IF _id IS NULL THEN                 -- no UUID provided
      INSERT INTO myschema.person(id)  -- see below about schema name
      VALUES (DEFAULT);                -- use the key word DEFAULT
   ELSE                                -- UUID provided
      INSERT INTO myschema.person(id)
      VALUES (_id);
   END IF;

   RETURN FOUND;                       -- (return value pointless so far)
END
$func$;

避免对参数和涉及的表列使用相同的名称。由于函数参数在函数体中的每个 SQL 命令中都是可见的，它会导致非常困惑的命名冲突(即使 INSERT 的目标列在现代 Postgres 中不受此影响).我使用 _id 作为参数名称。

未在 INSERT 中提及的其他列的默认值自动填充强>.我使用关键字 DEFAULT 因为我们需要为 INSERT 列出至少一个目标列。

boolean 返回值在此演示中毫无意义，因为它始终 true(除非您有可能跳过该行的触发器)。

相关答案以及可能的替代方案和大量解释:

• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

旁白:您应该在 SECURITY DEFINER 函数中对所有 函数和表名称进行模式限定 - 或(如果您不确定，可能会更好)显式设置 search_path 以抵御可能的攻击。更多:

• How does the search_path influence identifier resolution and the "current schema"