我在尝试在 plpgsql 中定义我无法解释的函数时遇到语法错误。由于某种原因,此代码将始终失败,并显示syntax error at or nor "LOOP"
:
CREATE OR REPLACE FUNCTION ST_Dilate(
in_geom GEOMETRY,
scale_factor FLOAT,
tol FLOAT DEFAULT 0.001,
guess FLOAT DEFAULT 1,
safety INTEGER DEFAULT 1000
)
RETURNS GEOMETRY AS
$$
DECLARE
step FLOAT = guess/2;
current_area FLOAT = ST_Area(ST_Buffer(in_geom, buff));
desired_area FLOAT = ST_Area(in_geom)*scale_factor;
dev FLOAT = (current_area-desired_area)/desired_area;
old_dev FLOAT;
safety_counter INTEGER = 0;
BEGIN
WHILE ABS(dev) > tol LOOP
IF safety_counter > safety THEN /* Can't find suitable distance after many iterations, terminate the function to prevent extreme hangs. */
RAISE NOTICE 'Could not find suitable buffer distance when dilating geom % after % iterations, NULL geometry returned instead. Consider adjusting "guess" parameter value or initial step size.', geom, safety;
RETURN NULL;
END IF;
safety_counter = safety_counter + 1;
/* Save the old deviation to be compared later to the new one later, calculate the current area and the new deviation from the desired area. */
old_dev = dev;
current_area = ST_Area(ST_Buffer(in_geom, guess));
dev = (current_area - desired_area) / desired_area;
IF dev < 0 THEN /* Current area is smaller than desired area, increase the buffer distance by the step. */
guess = guess + step;
ELSE IF dev > 0 THEN /* Current area is larger than desired area, decrease the buffer distance by the step. */
guess = guess - step;
ELSE /* Technically shouldn't ever happen because then ABS(dev) is indeed lesser than tol but here just in case. */
EXIT;
END IF;
IF dev * old_dev < 0 THEN /* Negative value indicates difference of sign, which means we just overestimated the area after underestimating it or vice versa, need to half the step. */
step = step * 0.5;
END IF;
END LOOP; /* syntax error here */
RETURN ST_Buffer(in_geom, guess);
END
$$
LANGUAGE plpgsql;
我根本没有看到语法错误。更令人困惑的是,如果我注释掉这个块:
IF safety_counter > safety THEN /* Can't find suitable distance after many iterations, terminate the function to prevent extreme hangs. */
RAISE NOTICE 'Could not find suitable buffer distance when dilating geom % after % iterations, NULL geometry returned instead. Consider adjusting "guess" parameter value or initial step size.', geom, safety;
RETURN NULL;
END IF;
safety_counter = safety_counter + 1;
代码成功执行一次,但第二次立即失败并出现相同的错误。
我对此完全感到困惑。我已经一次注释掉了每个块以及试图找到问题的单个行,我在任何地方都找不到语法错误,但它一直在抱怨。
我在 postgresql 9.6 上。
如果要获取单个IF
语句,请使用ELSIF
:
IF dev < 0 THEN /* Current area is smaller than desired area, increase the buffer distance by the step. */
guess = guess + step;
ELSIF dev > 0 THEN /* Current area is larger than desired area, decrease the buffer distance by the step. */
guess = guess - step;
ELSE /* Technically shouldn't ever happen because then ABS(dev) is indeed lesser than tol but here just in case. */
EXIT;
END IF;
阅读有关控制结构的文档。
你在END LOOP
之前错过了END IF
。看到它简化和预期:
IF dev < 0 THEN
guess = guess + step;
ELSE
IF dev > 0 THEN
guess = guess - step;
ELSE
EXIT;
END IF;
END IF; -- this is missing