PostgreSQL Function: 'LOOP' keyword missing in SQL statement

Data and Databases
1

Hey everyone, I’m stuck with a PostgreSQL error. My function to rank players by height isn’t working. The error says:

ERROR: missing "LOOP" at end of SQL expression
CONTEXT: compilation of PL/pgSQL function "player_height_rank" near line 9

Here’s a simplified version of my code:

CREATE FUNCTION athlete_size_position(name_first TEXT, name_last TEXT) RETURNS INTEGER AS $$
DECLARE
  position INTEGER := 0;
  gap INTEGER := 0;
  last_size INTEGER := NULL;
  athlete RECORD;
BEGIN
  FOR athlete IN SELECT 
    ((a.height_m * 100) + (a.height_cm)) AS total_height, 
    a.name_first, 
    a.name_last
  FROM athletes a
  WHERE a.name_first = $1 AND a.name_last = $2
  ORDER BY total_height DESC, a.name_first, a.name_last
  -- LOOP keyword missing here?
    IF athlete.total_height = last_size THEN
      gap := gap + 1;
    ELSE
      position := position + gap + 1;
      gap := 0;
      last_size := athlete.total_height;
    END IF;
    
    IF athlete.name_last = $2 AND athlete.name_first = $1 THEN
      RETURN position;
    END IF;
  END LOOP;
  
  RETURN -1;
END;
$$ LANGUAGE plpgsql;

Can someone help me spot where the ‘LOOP’ keyword should go? Thanks!

2

The error you’re encountering is indeed due to a missing ‘LOOP’ keyword. In PostgreSQL’s PL/pgSQL, when using a FOR loop with a SELECT statement, you must explicitly include the LOOP keyword immediately after the ORDER BY clause. Here is the corrected snippet:

FOR athlete IN SELECT 
  ((a.height_m * 100) + (a.height_cm)) AS total_height, 
  a.name_first, 
  a.name_last
FROM athletes a
WHERE a.name_first = $1 AND a.name_last = $2
ORDER BY total_height DESC, a.name_first, a.name_last
LOOP
  -- Your existing logic here
END LOOP;

Implementing this change should resolve the missing keyword error. Additionally, ensure that your function returns a value for all execution paths to prevent other potential issues.

3

yo maya, looks like u got it sorted already. just wanna add, make sure ur data’s clean before rankin. had issues w/ height once cuz some dudes put cm instead of m lol. also, u might wanna consider usin RANK() or DENSE_RANK() functions. way easier imo. good luck!

4

hey maya! looks like ur missing the LOOP keyword after ur SELECT statement. try adding it right after the ORDER BY clause, before the IF statement. that should fix the error. curious tho, how many athletes are u ranking? must be a big dataset!