Блог

Используя PostgreSQL, мне нужно автоматически установить значение поля во вставленной строке на основе вывода функции, использующей другое поле из той же самой вставляемой строки. Например, учитывая таблицу:

 CREATE TABLE my_table
(
    id           UUID                     DEFAULT uuid_generate_v4(),
    code         VARCHAR(50) PRIMARY KEY,
    value        VARCHAR(50),
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT now()
)
 

При вставке новой строки, например

 INSERT INTO my_table (code, value)
VALUES ('a','a'), 
       ('b','b');
 

Мне нужно, чтобы поле my_table.id автоматически устанавливалось для этих двух новых строк на основе выходных данных этой параметризованной функции на основе value :

 CREATE FUNCTION gen_deterministic_id(val varchar)
    RETURNS TRIGGER AS
$
BEGIN
    NEW.id = md5('xx' ||  val)::uuid;
    RETURN NEW;
END;
$ language 'plpgsql';
 

ПРИМЕЧАНИЕ: мне действительно нужно, чтобы функция была параметризуемой (чтобы иметь возможность передавать любой параметр).

Псевдокод будет выглядеть примерно так:

 for each new row:
    row.id = gen_deterministic_id(row.value)
    then insert_to_table
 

Ребята, вы знаете, возможно ли это реализовать, используя, например, триггеры или тому подобное в PostgreSQL?

TRIGGER возвращаемые функции не принимают никаких параметров. Но вы можете использовать значения непосредственно в функции с NEW.value

Вариант 1: вы можете создать триггер:

демо: db<>скрипка

 CREATE FUNCTION gen_deterministic_id()
    RETURNS TRIGGER AS
$
BEGIN
    NEW.id = md5('xx' || NEW.value)::uuid;
    RETURN NEW;
END;
$ language 'plpgsql';

CREATE TRIGGER generate_uuid
  BEFORE INSERT
  ON my_table
  FOR EACH ROW
  EXECUTE PROCEDURE gen_deterministic_id();
 

Вариант 2: вы можете использовать новую функцию Postgres 12: сгенерированные столбцы:

демо: db<>скрипка

 CREATE TABLE my_table
(
    id           uuid GENERATED ALWAYS AS (md5('xx' ||  value)::uuid) STORED, -- 1
    code         VARCHAR(50) PRIMARY KEY,
    value        VARCHAR(50),
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT now()
);
 

1. В этом столбце хранятся вычисленные результаты на основе вставленной записи.