postgresql - 使用触发器函数中的新功能动态更改列值

Question

是否可以使用触发器函数动态更改 NEW 中的列值？

触发器:

CREATE TRIGGER lowercase_username_on_insert_trigger BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE PROCEDURE lowercase_on_insert('username');

CREATE TRIGGER lowercase_email_on_insert_trigger BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE PROCEDURE lowercase_on_insert('email');

触发函数:

CREATE OR REPLACE FUNCTION lowercase_on_insert()
RETURNS trigger
AS $lowercase_on_insert$
    BEGIN
        -- e.g., NEW.username = LOWER(NEW.username)
        --  -OR- NEW.email = LOWER(NEW.email)
        EXECUTE FORMAT('NEW.%s = LOWER(NEW.%s);', TG_ARGV[0], TG_ARGV[0]);
        RETURN NEW;
    END
$lowercase_on_insert$ LANGUAGE plpgsql;

每当我运行 INSERT 时，我都会收到 ERROR: syntax error at or new "NEW"。

这是我的 table :

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(55) NOT NULL UNIQUE,
    email VARCHAR(120) NOT NULL UNIQUE
);

Answer 1

如下更改您的触发器函数

CREATE OR REPLACE FUNCTION lowercase() RETURNS trigger AS $$
    BEGIN        
        NEW.username = LOWER(NEW.username);
        NEW.email = LOWER(NEW.email);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

并赋值

CREATE TRIGGER tgrr BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE PROCEDURE lowercase();

或

你可以像这样在插入/更新中直接应用lower()函数

insert into users(username,email) values (lower('Jerry'),lower('JeRRY@mail.COM')); 

update users set username=lower('JERRY'),email=lower('JERRY@MAIL.COM') where id =1