PostgreSQL插入触发器进行串联 [英] Postgresql insert trigger to concatenate

问题描述

我想创建一个触发器以将我的列与事件插入前连接起来，但是查询无法正常工作，并且出现错误：

I want to create a trigger to concatenate my column with event "before insert", but the query didn't work and i got an error:

SQL错误：

SQL error:

错误： SET处或附近的语法错误第4行：SET new.fullname =
CONCAT（new.first_name ，''，new.mid_name，...

ERROR: syntax error at or near "SET" LINE 4: SET new.fullname = CONCAT(new.first_name, '', new.mid_name, ...

In statement:
CREATE TRIGGER insert_trigger
BEFORE INSERT ON t_employees
FOR EACH ROW
SET new.fullname = CONCAT(new.first_name, '', new.mid_name, '', new.last_name);

推荐答案

这是一个可行的解决方案：

Here's a working solution:

CREATE TEMPORARY TABLE t_employees (
    first_name TEXT,
    mid_name TEXT,
    last_name TEXT,
    fullname TEXT
);

CREATE OR REPLACE FUNCTION set_fullname()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.fullname = NEW.first_name || ' ' || NEW.mid_name || ' ' || NEW.last_name;
        RETURN NEW;
    END;
    $$ language 'plpgsql';


CREATE TRIGGER set_fullname_trigger
    BEFORE INSERT OR UPDATE ON t_employees
    FOR EACH ROW
    EXECUTE PROCEDURE set_fullname();


SET client_min_messages TO 'debug';
INSERT INTO t_employees VALUES ('fname1', 'mname1', 'lname1');
SELECT * FROM t_employees;
UPDATE t_employees SET first_name = 'updated-first-name';
SELECT * FROM t_employees;

这篇关于PostgreSQL插入触发器进行串联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！