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.

you can keep the id and make generated column see https://www.postgresql.org/docs/current/ddl-generated-columns.html — nbk, Aug 08 '23 at 14:29
@nbk thk but how can i call my function from this generated column? I made this: "identifier TEXT generated always as (encode_id(id)) STORED;" but it returns that the function doesn't exist — iamleoprosy, Aug 09 '23 at 11:05
please try to search for a solution like https://stackoverflow.com/questions/69493668/postgresql-generated-column-as-output-from-a-function — nbk, Aug 09 '23 at 11:41

score 0 · Answer 1 · answered Aug 20 '23 at 12:16

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', 'john.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johny Doe', 'johny.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johne Doe', 'johne.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johni Doe', 'johni.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johnyee Doe', 'johnyee.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johnlee Doe', 'johnlee.doe@example.com');
INSERT INTO persons (name, email) VALUES ('Johnree Doe', 'johnree.doe@example.com');

To verify this

SELECT * FROM persons;

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

1 Answers1