如何正确创建触发器更新同表PostGIS geom列及最优实现方式选型

优先选择服务端实现，优势如下：

• 避免客户端传参被恶意篡改，数据合法性更可控
• 逻辑统一在服务端维护，不会因为前端代码迭代出现逻辑不一致
• 规避客户端拼接SQL片段带来的注入风险

方案1：直接在PHP插入逻辑处理（最简方案） #

你之前客户端方案报错的核心原因是：你把ST_SetSRID这类PostGIS函数当成字符串通过参数绑定传入，预处理机制会把整个字符串当成普通文本插入geometry类型的geom列，自然会解析失败。本地能运行是因为本地环境大概率未开启严格参数绑定，直接把函数片段拼入SQL执行了，线上环境开启严格校验后就会报错。
直接修改PHP的预处理逻辑即可，不需要前端传geom参数：

$lon = $_POST['lon'];
$lat = $_POST['lat'];
// 直接在SQL语句中调用PostGIS函数生成geom值
$set_interview = $conn->prepare("INSERT INTO datos(lon,lat,geom) VALUES (:lon,:lat, ST_SetSRID(ST_MakePoint(:lon,:lat),4326))");
$set_interview->bindParam(':lon', $lon);
$set_interview->bindParam(':lat', $lat);

方案2：触发器逻辑修复 #

你之前的触发器存在两个核心问题：

1. BEFORE类触发器不需要写UPDATE语句更新表，直接修改NEW变量的字段值即可，否则会触发递归调用死循环
2. 拼接geometry字符串时没有正确取到当前行的lon、lat值，把字段名当成了字符串的一部分

正确的触发器写法如下，可同时覆盖插入、更新场景：

create or replace function SP_crearGeometry() returns trigger
as
$$
begin 
  -- 直接给NEW的geom字段赋值即可
  NEW.geom = ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
  return NEW;
End
$$
language plpgsql;

-- 插入前自动生成geom
create trigger TR_datos_before_insert
before insert on datos
for each row
execute procedure SP_crearGeometry();

-- lon、lat字段更新时自动同步geom
create trigger TR_datos_before_update
before update of lon, lat on datos
for each row
execute procedure SP_crearGeometry();

使用触发器方案后，PHP端插入、更新时只需要传入lon、lat参数即可，不需要处理geom字段。

内容的提问来源于stack exchange，提问作者Ander Canales Medina