syntax error at "END LOOP;" part of WHILE loop in plpgsql

Question

I am getting a syntax error when trying to define a function in plpgsql that I cannot explain. For some reason, this code will always fail with 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;

I don't see the syntax error at all. What is more confusing is that if I comment out this block:

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;

the code executes once successfully, but then immediately fails with the same error the second time around.

I'm completely confused about this. I have commented out every block one at a time as well as individual lines trying to find the problem and I cannot find a syntax error anywhere, and yet it keeps complaining.

I'm on postgresql 9.6.

klin · Accepted Answer · 2018-04-17 17:03:24Z

2

Use ELSIF if you want to get a single IF statement:

    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;

Read in the documentation about Control Structures.

answered Apr 17, 2018 at 17:03

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam · Accepted Answer · 2018-04-17 17:05:10Z

1

You are misssing END IF before END LOOP. See it simplified and intended:

    IF dev < 0 THEN
        guess = guess + step;
    ELSE 
        IF dev > 0 THEN
            guess = guess - step;
        ELSE
            EXIT;
        END IF;
    END IF; -- this is missing

answered Apr 17, 2018 at 17:05

Adam

5,6296 gold badges35 silver badges41 bronze badges

1 Comment

wfgeo Over a year ago

technically this would work but the problem was that I was writing ELSE IF when i meant to write ELSIF

Collectives™ on Stack Overflow

syntax error at "END LOOP;" part of WHILE loop in plpgsql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related