由于要更新的记录已在数据库中找到，因此应首先使用UPDATE语句更新当前行。可以使用CASE表达式来执行此操作，该表达式将根据“* 移动的_phone*"有条件地分配“country”值。

UPDATE "info"
SET country = CASE WHEN LEFT(mobile_phone,2) = '51' THEN 'PE'
                   WHEN LEFT(mobile_phone,2) = '56' THEN 'CL'
                   WHEN LEFT(mobile_phone,2) = '57' THEN 'CO' END;

然后创建相应的触发器，它将在插入之前更新您的记录。

• 函数“update_country（）"，它返回一个触发器，该触发器使用NEW访问插入行中的值并更新其国家值
• 触发器创建定义（您也包含在帖子中），它使用“update_country（）”更新插入语句中的记录。

CREATE OR REPLACE FUNCTION update_country()
RETURNS TRIGGER AS
'
BEGIN
    NEW.country = CASE WHEN LEFT(NEW.mobile_phone,2) = ''51'' THEN ''PE''
                       WHEN LEFT(NEW.mobile_phone,2) = ''56'' THEN ''CL''
                       WHEN LEFT(NEW.mobile_phone,2) = ''57'' THEN ''CO'' END;
    RETURN NEW;
END;
'
LANGUAGE plpgsql;

CREATE TRIGGER update_country_trigger
BEFORE INSERT ON "info"
FOR EACH ROW
EXECUTE PROCEDURE update_country();

检查here演示。

您可以使用"生成的列"。使用"生成的列"不能执行以下操作
update info set country = 'x';或
insert into info(country) value ('x');
https://www.postgresql.org/docs/current/ddl-generated-columns.html

BEGIN;
CREATE temp TABLE info (
    mobile_phone text
    , country text GENERATED ALWAYS AS ( CASE WHEN
    LEFT (mobile_phone , 2) = '51' THEN
        'PE'
    WHEN
    LEFT (mobile_phone , 2) = '52' THEN
        'XY'
    WHEN
    LEFT (mobile_phone , 2) = '53' THEN
        'AB'
    WHEN
    LEFT (mobile_phone , 2) = '54' THEN
        'CD'
    WHEN
    LEFT (mobile_phone , 2) = '55' THEN
        'EF'
    WHEN
    LEFT (mobile_phone , 2) = '56' THEN
        'CL'
    WHEN
    LEFT (mobile_phone , 2) = '57' THEN
        'CO'
    ELSE
        'NA'
    END) STORED
) ON COMMIT DROP;
INSERT INTO info (mobile_phone)
    VALUES ('51682815')
    , ('51261728')
    , ('56682815')
    , ('56261728')
    , ('57682815')
    , ('57261728');
TABLE info;
END;