如何在PostgreSQL触发器函数中获取列类型并修正报错

你遇到的错误核心原因是：pg_typeof()返回的是PostgreSQL内部的类型OID对象，而不是字符串类型，直接和字符串'geometry'比较会触发类型不匹配的报错（PostgreSQL无法将字符串转换为oid类型）。另外你的原函数里还多了一个多余的END IF;，这也会导致语法错误，需要先去掉。

下面给你几种正确的写法：

方法1：将pg_typeof结果转为文本后比较 #

把pg_typeof(NEW.shape)的结果通过::text强制转换为字符串，再和'geometry'比较：

CREATE FUNCTION point_xy() RETURNS trigger AS $point_xy$
BEGIN
    IF pg_typeof(NEW.shape)::text = 'geometry' AND GeometryType(NEW.shape) = 'POINT' THEN
        NEW.x = st_x(NEW.shape);
        NEW.y = st_y(NEW.shape);
    END IF;
    RETURN NEW;
END $point_xy$ LANGUAGE plpgsql;

方法2：使用pg_type_name函数获取类型名称 #

pg_type_name()函数可以接受类型OID，直接返回对应的类型名称字符串，这样更直观：

CREATE FUNCTION point_xy() RETURNS trigger AS $point_xy$
BEGIN
    IF pg_type_name(pg_typeof(NEW.shape)) = 'geometry' AND GeometryType(NEW.shape) = 'POINT' THEN
        NEW.x = st_x(NEW.shape);
        NEW.y = st_y(NEW.shape);
    END IF;
    RETURN NEW;
END $point_xy$ LANGUAGE plpgsql;

方法3：简化检查（推荐） #

如果你的shape字段在表结构中已经定义为geometry类型，那么触发器触发时NEW.shape的类型必然是geometry，此时可以省略pg_typeof的检查，只验证几何类型是否为POINT即可，这样代码更简洁：

CREATE FUNCTION point_xy() RETURNS trigger AS $point_xy$
BEGIN
    IF GeometryType(NEW.shape) = 'POINT' THEN
        NEW.x = st_x(NEW.shape);
        NEW.y = st_y(NEW.shape);
    END IF;
    RETURN NEW;
END $point_xy$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者barteloma