Question 255952 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Syntax error in plpgsql function with if statement and variable

I’m new to SQL and don’t think I understand how to write if else statements. The following code block produces a syntax error at the if statement.

create or replace function weight_from_days_ago (days_ago int, difficulty int, user_level int, root_score int)
returns int as $$
begin
    declare average_days_ago int := (select avg(days_ago));
    if average_days_ago = 0 then 
    average_days_ago := 1;
    return ((select coalesce(days_ago, 0))/average_days_ago) + abs(difficulty - user_level) + root_score);
end;
$$ language plpgsql;

This function, without the additional variable, also produces a syntax error when run.

begin
    return (select coalesce(days_ago, 0)/(if ((select avg(days_ago)) = 0) then 1 else (select avg(days_ago))) + abs(difficulty - user_level) + root_score);
end;
Tags:

2

Answers


  1. 0

    Syntax error isn’t that difficult to fix, just follow error messages database engine reports; it’s about superfluous closing bracket, missing end if. Moreover, if if condition isn’t satisfied, function ends without returning any value (which is invalid) so you have to fix it. How? ELSE might be an obvious choice.

    Furthermore, what is the purpose of selecting average value of a scalar in parameter? What is average of 13? It is 13, of course … so, why averaging it at all?

    Anyway: such a code compiles and returns result (see fiddle:

    create or replace function weight_from_days_ago (days_ago int, difficulty int, user_level int, root_score int)
returns int as $$
  declare average_days_ago int := avg(days_ago);
begin  
    if average_days_ago = 0 then 
      average_days_ago := 1;
      return ((select coalesce(days_ago, 0))/average_days_ago) + abs(difficulty - user_level) + root_score;
    else
      return null;
    end if;
end;

$$ language plpgsql;

    However, I’d suggest you to think it over and fix logical issues.

    Login or Signup to reply.
  2. 0

    There are obvious syntax errors and nonsense like Littlefoot already pointed out. Read the manual here and here.

    Moreover, there is nothing in your function that would require a procedural language like PL/pgSQL to begin with. An expression is evaluated. Just use a plain SQL function:

    CREATE OR REPLACE FUNCTION weight_from_days_ago (days_ago int, difficulty int, user_level int, root_score int)
  RETURNS int
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT COALESCE(days_ago, 0) / GREATEST(days_ago, 1)
     + abs(difficulty - user_level)
     + root_score;
$func$;

    See:

    Also, there is no IF in SQL (only CASE). That makes your 2nd attempt invalid. Don’t confuse SQL syntax with PL/pgSQL syntax. I use GREATEST() instead (which would also catch negative integers).

    You can also use a "standard SQL function" in Postgres 14 or newer:

    CREATE OR REPLACE FUNCTION weight_from_days_ago (days_ago int, difficulty int, user_level int, root_score int)
  RETURNS int
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN COALESCE(days_ago, 0) / GREATEST(days_ago, 1)
     + abs(difficulty - user_level)
     + root_score;

    Even a bit shorter and faster, yet. See:

    See also:

    Aside: abs(difficulty - user_level) looks counter-intuitive, too. Lower levels get the same bonus as higher levels?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search