PostgreSQL CASE statement syntax issues-CodePudding

I receive a syntax error for the following:

CREATE OR REPLACE FUNCTION my_function(old_num INTEGER)
   returns INTEGER
   language plpgsql
AS
$$
DECLARE new_num INTEGER;
BEGIN
   CASE
      WHEN (old_num IN (1, 2, 3, 4)) THEN new_num = 10
      WHEN (old_num IN (5, 6, 7, 8)) THEN new_num = 20
      ELSE new_num = 0
   END;

   RETURN new_num;
END;
$$;

The error points to the second WHEN. I've tried using all kinds of combinations of parenthesis. What is wrong with this syntax??

CodePudding user response：

A CASE expression results in a value. You want it to result in 10, 20 or 0, which you then want to assign to your variable new_num.

new_num :=
 CASE
    WHEN (old_num IN (1, 2, 3, 4)) THEN 10
    WHEN (old_num IN (5, 6, 7, 8)) THEN 20
    ELSE 0
 END;

CodePudding user response：

CREATE OR REPLACE FUNCTION my_function(old_num INTEGER)
   returns INTEGER
   language plpgsql
AS
$$
DECLARE new_num INTEGER;
BEGIN
   new_num := (CASE 
      WHEN old_num in (1, 2, 3, 4) THEN 10
      WHEN old_num in (5, 6, 7, 8) THEN 20
      ELSE 0
   END);

   RETURN new_num;
END;
$$;

CodePudding user response：

Alternate solution:

CREATE OR REPLACE FUNCTION public.my_function(old_num integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE new_num INTEGER;
BEGIN
   SELECT INTO new_num
   CASE
      WHEN (old_num IN (1, 2, 3, 4)) THEN 10
      WHEN (old_num IN (5, 6, 7, 8)) THEN 20
      ELSE 0
   END;

   RETURN new_num;
END;
$function$

select my_function(1);
 my_function 
-------------
          10

select my_function(6);
 my_function 
-------------
          20

select my_function(12);
 my_function 
-------------
           0

CodePudding user response：

You can convert your function to a SQL function and reduce it to a single Select statement. See Demo.

create or replace function public.my_function(old_num integer)
 returns integer
 language sql
as $$
   select  case when (old_num in (1, 2, 3, 4)) then 10
                when (old_num in (5, 6, 7, 8)) then 20
                else 0
           end;
$$;