Inserting a COALESCE(NULL,default)
<p>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.</p>

<p>Each table has this at it's core:</p>

<pre><code>CREATE TABLE IF NOT EXISTS person (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
</code></pre>

<p>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:</p>

<pre><code>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;
$$;
</code></pre>

<p>I've tried this:</p>

<pre><code>INSERT INTO person ( id ) VALUES (
    COALESCE(id, default),
);
</code></pre>

<p>and this:</p>

<pre><code>INSERT INTO person ( id ) VALUES (
  CASE WHEN id IS NULL THEN default ELSE id END
);
</code></pre>

<p>This works, but it repeats the gen_random_uuid() code:</p>

<pre><code>INSERT INTO person ( id ) VALUES (
  COALESCE(id, gen_random_uuid()),
);
</code></pre>

<p>similarly this works too but has the same problems:</p>

<pre><code>INSERT INTO person ( id ) VALUES (
    CASE WHEN id IS NULL THEN gen_random_uuid() ELSE id END
);
</code></pre>

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

<p>Would this be better done with <code>plpgsql</code>?</p>