CODEWITHSUNDEEP
postgresqlplpgsql
Kim
Kim

Reputation: 3494

plpgsql - syntax error at or near "if"

I'm struggling to get the syntax correct for a plpgsql if statement.

I'd like to use a different where clause depending on the value of the m2 argument to the function.

But I get ERROR: syntax error at or near "if"

create or replace function some_func(m1 int, m2 int)
returns table(match_id int) as
$$
begin
  return query
  select m.match_id from match as m
  if m2 < 1 then  -- ERROR:  syntax error at or near "if"
    where m.match_id > m1;
  else
    where m.match_id > m1 and m.match_id < m2;
  end if;
end;
$$ language 'plpgsql';

The documentation says I should do 

39.6.2.2. IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

But it seems I've done that, so I must be misunderstanding some other aspect of how plpgsql works.

Upvotes: 0

Views: 2032

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

You need a case statement there, rather than an if statement. For reference only, since it's a terrible query, it should look more like this:

return query
select m.match_id from match as m
 where case
       when m2 < 1 then m.match_id > m1
       else             m.match_id > m1 and m.match_id < m2
       end;

In your case just rewrite the query without the case:

return query
select m.match_id from match as m
 where m.match_id > m1
   and (m2 < 1 or m.match_id < m2);

Upvotes: 2

Related Questions