How to automatically set a field based on another field when inserting a row in PostgreSQL?

user_8019519 0
Viewed 264

Using PostgreSQL, I need to automatically set a field value in an inserted row based on the output of a function using another field from the very same row that is being inserted. For example, given a table:

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

When inserting a new row e.g.

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

I need the field my_table.id to be automatically set for those two new rows based on the output of this parametrized function based on value:

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

NOTE: I really need the function to be parametrizable (to be able to pass any parameter).

The pseudocode would be something like this:

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

Do you guys know if this is possible to implement using e.g. triggers or the like in PostgreSQL?

postgresql
1 Answers

TRIGGER returning functions don't accept any parameters. But you can use the values directly in the function with NEW.value

Option 1: You can create a trigger:

demo:db<>fiddle

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

Option 2: You can use the new Postgres 12 feature: Generated Columns:

demo:db<>fiddle

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. This column keeps calculated results based on the inserted record.
user_39842210
Related