提问人：iamleoprosy 提问时间：8/8/2023 更新时间：8/20/2023 访问量：34
在SQL中插入表时如何调用函数修改值
How to call a function to modify a value when inserting into table in SQL
问：
我的 postgresql 数据库中有一个人员表，其列 id 为 bigserial 类型。我希望每次创建新人时都这样做，而不是为 id 设置一个简单的数字，拥有一个像 youtube 这样的数字。
为此，我创建了一个将 int 转换为复杂 id 的函数，但我不知道如何使用该字符串自动更新 te id。
答：
0赞
PGzlan
8/20/2023
#1
您可以尝试这样的事情并对其进行调整以满足您的需求
-- 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]');
验证这一点
SELECT * FROM persons;
评论