插入一个COALESCE（NULL，默认）

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未指定值时会发生的情况。根据此定义，null值仍然是“指定的”，因此不能使用默认值。

您关于某人可能不使用该功能的评论表明，触发器比简单的功能更适合您的要求。

https://www.postgresql.org/docs/current/static/plpgsql-
trigger.html

CREATE OR REPLACE FUNCTION default_id() RETURNS TRIGGER AS $default_id$
    BEGIN       
        IF (NEW.id IS NULL) THEN
            NEW.id := gen_random_uuid();
        END IF;
        RETURN NEW;
    END;
$default_id$ LANGUAGE plpgsql;

CREATE TRIGGER default_id_trigger
BEFORE INSERT OR UPDATE ON person
    FOR EACH ROW EXECUTE PROCEDURE default_id();

如果您确实想使用函数来执行此操作，那么最简单的方法就是在插入之前分配值：

CREATE OR REPLACE FUNCTION create_person(
    id UUID
) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    IF id IS NULL THEN
        id := gen_random_uuid();
    END IF;
    -- OR
    -- id := coalesce(id, gen_random_uuid());
    INSERT INTO person( id )
    VALUES (id);
    RETURN FOUND;
END;
$$;