我想编写触发器,在插入新行时更新表。我正在使用如下更新查询:
UPDATE table SET
geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
UPDATE table SET
geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
BEFORE
触发器,然后分配新值:CREATE OR REPLACE FUNCTION function_update()
RETURNS trigger AS
$BODY$
BEGIN
new.geom := ST_SetSRID(ST_MakePoint(new.longitude, new.latitude), 4326);
RETURN new;
END;
$BODY$
LANGUAGE plpgsql;
这只能在 BEFORE
触发器中完成:
CREATE TRIGGER triggerinsert BEFORE INSERT ON rdpr FOR EACH ROW EXECUTE PROCEDURE function_update();
CREATE OR REPLACE FUNCTION function_update()
RETURNS trigger AS
$BODY$
BEGIN
new.geom := ST_SetSRID(ST_MakePoint(new.longitude, new.latitude), 4326);
RETURN new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION function_update()
OWNER TO postgres;
我写了触发器来进行更新
CREATE TRIGGER triggerinsert
Before INSERT
ON rdpr
FOR EACH ROW
EXECUTE PROCEDURE function_update();
update
,因为它比简单更改插入的行要慢得多。 - user330315