CODEWITHSUNDEEP
sqldatabasepostgresqlloopsplpgsql
lilywang
lilywang

Reputation: 9

ERROR: missing "LOOP" at end of SQL expression

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;

Upvotes: 0

Views: 8618

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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

Upvotes: -1

light souls
light souls

Reputation: 728

In your WHERE clause semicolon is superfluous

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

correct that part and try again.

Upvotes: 1

Related Questions