插入一个COALESCE(NULL，默认) [英] Inserting a COALESCE(NULL,default)

问题描述

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

I have tables that use UUIDs. I want to be able to insert a new row with or without a UUID as sometimes the client will generate the UUID other times it won't.

每个表的核心是这个:

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

我正在尝试使用一个函数来插入行.我希望能够提供一个NULL ID并获取默认值(生成的UUID).我有这样的东西:

I'm trying to use a function to insert rows. I'd like to be able to hand a NULL id and get a default value (a generated UUID). I have something like this:

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()代码:

This works, but it repeats the gen_random_uuid() code:

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

同样也可以，但是存在相同的问题:

similarly this works too but has the same problems:

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

有没有一种方法可以不必重复gen_random_uuid()代码?

Is there a way to do this where I don't have to repeat the gen_random_uuid() code?

用plpgsql更好吗?

推荐答案

无法重用该列上定义的默认值.默认值仅用于定义INSERT未指定值时的情况.根据此定义，null值仍为指定"，因此无法使用默认值.

There's no way to re-use the defined default on the column. The default is only there to define what happens if an INSERT doesn't specify a value. By this definition a null value is still "specified" and therefore default can't be used.

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

Your comment that someone might not use the function indicates that a trigger is better for your requirements than a simple function.

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();

---

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

---

If you do want to do this with a function then the simplest way is just to assign the value before inserting:

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;
$$;

这篇关于插入一个COALESCE(NULL，默认)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！