ERROR: missing "LOOP" at end of SQL expression

Question

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

Here is my code:

CREATE OR REPLACE FUNCTION player_height_rank (irstname VARCHAR, lastname VARCHAR) RETURNS INT AS $$ 
    
DECLARE
  rank INT := 0;
  offset INT := 0;
  tempValue INT := NULL;
  r record;
BEGIN
  FOR r IN SELECT ((p.h_feet * 30.48) + (p.h_inches * 2.54)) AS height, p.firstname, p.lastname
    FROM players p
    WHERE p.firstname = $1 AND p.lastname = $2;
    ORDER BY ((p.h_feet * 30.48) + (p.h_inches * 2.54)) DESC, p.firstname, p.lastname  
  LOOP
    IF r.height = tempValue THEN
      offset := offset + 1;
    ELSE
      rank := rank + offset + 1;
      offset := 0;
      tempValue := r.height;
    END IF;
    
    IF r.lastname = $4 AND r.lastname = $3 THEN
      RETURN rank;
    END IF;
  END LOOP;
        
  -- not in DB
  RETURN -1;
END; $$ LANGUAGE plpgsql;

Please post questions, not an error and some code. What did you try? Where did you get stuck? — Craig Ringer
– Craig Ringer, Commented Mar 20, 2017 at 9:07

light souls · Accepted Answer · 2017-03-19 20:32:48Z

1

In your WHERE clause semicolon is superfluous

WHERE p.firstname = $1 AND p.lastname = $2; -- delete semicolon

correct that part and try again.

answered Mar 19, 2017 at 20:32

light souls

7381 gold badge9 silver badges18 bronze badges

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

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-27 16:39:23Z

I got the same error below:

ERROR: missing "LOOP" at end of SQL expression

Because I used WHILE, FOR or FOREACH statement without LOOP statement as shown below:

DO $$
DECLARE
  num INT := 0;
BEGIN
  WHILE num <= 3; -- Here
END
$$;

Or:

DO $$
DECLARE
  num INT := 0;
BEGIN
  FOR num IN 0..3; -- Here
END
$$;

Or:

DO $$
DECLARE
  num INT := 0;
  nums INT[] := ARRAY[0,1,2,3]::INT[];
BEGIN
  FOREACH num IN ARRAY nums; -- Here
END
$$;

So, I set LOOP statement for WHILE, FOR or FOREACH statement as shown below:

DO $$
DECLARE
  num INT := 0;
BEGIN
  WHILE num <= 3 LOOP
    RAISE INFO 'num is %.', num;
    num := num + 1;
  END LOOP;
END
$$;

Or:

DO $$
DECLARE
  num INT := 0;
BEGIN
  FOR num IN 0..3 LOOP
    RAISE INFO 'num is %.', num;
  END LOOP;
END
$$;

Or:

DO $$
DECLARE
  num INT := 0;
  nums INT[] := ARRAY[0,1,2,3]::INT[];
BEGIN
  FOREACH num IN ARRAY nums LOOP
    RAISE INFO 'num is %.', num;
  END LOOP;
END
$$;

Then, the error was solved as shown below:

INFO:  num is 0.
INFO:  num is 1.
INFO:  num is 2.
INFO:  num is 3.
DO

Collectives™ on Stack Overflow

ERROR: missing "LOOP" at end of SQL expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related