How to call a function to modify a value when inserting into table in SQL

Question

I have a table of persons in my postgresql database with the column id with a bigserial type. I want that everytime a new person is created instead of setting a simple number for the id, having one like youtube ones.

To make that i created a function that transform an int into a complex id but I don't how to update te id with that string automatically.

Answer 1

You can try something like this and tweak it to match your needs

-- Dummy table to test
CREATE TABLE persons (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION generate_complex_id() RETURNS TRIGGER AS $$
DECLARE
    hash TEXT;
    result TEXT;
BEGIN
-- Hash will have hexadecimal characters that are hard to convert directly into numeric value
  hash := UPPER(SUBSTRING(string_agg(MD5(CAST(RANDOM() AS TEXT)), ''), 1, 5)) FROM generate_series(1, CAST(CEIL(2/ 32.) AS INTEGER));
-- Wrap the `hash` value such that it can be turned into a numeric value (modify the `int8` to match your desired type)
  EXECUTE 'SELECT x' || quote_literal(hash) || '::int8' INTO result;
-- Concat the date and the converted hash
  NEW.id := CONCAT(TO_CHAR(CURRENT_DATE, 'YYYYMMDD'), result);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Creating a trigger so that it applies to each insert
CREATE TRIGGER test_trigger BEFORE INSERT ON persons FOR EACH ROW EXECUTE FUNCTION generate_complex_id();

-- Dummy table
INSERT INTO persons (name, email) VALUES ('John Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johny Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johne Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johni Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johnyee Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johnlee Doe', '[email protected]');
INSERT INTO persons (name, email) VALUES ('Johnree Doe', '[email protected]');

To verify this

SELECT * FROM persons;