sql - 我只想在 PostgreSQL 中做一个简单的循环

Question

在 PostGIS SQL Editor 中，我只想做一个循环来使这个功能起作用

For Loopid = 0 to 1000, then execute the following statments:

UPDATE public.globaldtm 
SET "UTM" = loopid 
WHERE rid IN (SELECT rid FROM globaldtm WHERE ST_Intersects(rast,(select geom from utm where gid =loopid)));

如何在此 SQL 编辑器中编写简单代码以使循环工作。谢谢。

Answer 1

使用设置返回 generate_series() update statement 的可选 FROM 子句中的函数:

UPDATE public.globaldtm 
SET UTM = loopid
FROM generate_series(0, 1000) as x(loopid) -- Added this line
WHERE rid IN (SELECT rid FROM globaldtm WHERE ST_Intersects(rast,(select geom from utm where gid =loopid)));

这是您的查询，添加了这一行:

FROM generate_series(0, 1000) as x(loopid)

---

查看 live demo on SQLFiddle一个类似的、简化的查询。