Как в табличке сделать логику "если передали null, то пусть будет значение default?

Question

Есть табличка с юзерами

create type "role" as enum ('admin', 'user');

create table "user" (
  id     bigserial primary key,
  name   text   default null,
  avatar text   default null,
  role   "role" not null default 'user'
);

И есть функция для создания нового юзера

create function create_user(role role)
  returns "user" as
$$
insert into "user" (role)
values (create_user.role)
returning *
$$
  language sql volatile;

Когда я вызываю эту функцию через node javascript:

function createUser(role) {
  return await db.query(sql`select * from create_user(${role})`)
}

Я хочу как бы не знать в яваскрипте, как именно устроена база данных, то есть я не хочу логику "если роль не передана то пусть будет такая-то" реализовывать, всё должно быть именно на уровне базы данных сделанно, а именно на уровне таблчики. Но моя библиотека, npm пакет pg, если я передаю в интерполяцию значения undefined, она его превращает в null, и Postgress думает что я явно null хочу записать. Null передается в postgres функцию, а от туда передается в табличку. Как же сделать так, чтобы если табличка увидела что в ней null пытаюся записать, то применяла логику поля role DEFAULT 'user'? Разумеется без триггеров желательно.

Неужели для такой простой вещи не хватит sql и придется использовать plpgsql и вручную там null переписывать на нужное значение?

Answer 1

Короче нет, sql на такое чудо не способен =) вот решение на plpgsql:

create function create_user(role role)
  returns "user" as
$$
declare
  created_user "user";
begin

  if create_user.role is not null then
    insert into "user" (role)
    values (create_user.role)
    returning * into created_user;
  else
    insert into "user" (role)
    values (default)
    returning * into created_user;
  end if;

  return created_user;
end
$$
  language plpgsql volatile;

Жесть конечно =) выразительности этим языкам яяяявно не хватает =)

Answer 2

Собственно, украдено оттуда:

WITH t AS (
  INSERT INTO "user" (other_param)
  SELECT 'other_param'
   WHERE $1 IS NULL
)
INSERT INTO "user" (role, other_param)
SELECT $1, 'other_param'
 WHERE $1 IS NOT NULL
;

Условия взаимоисключающие, так что выполнится только один из INSERT'ов. Модифицировать для случая с одним параметром или для возвращения результата несложно.

БЛОГ НА HUSL

Как в табличке сделать логику "если передали null, то пусть будет значение default?

Ответы (2 шт):