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

PostgreSQL CASE statement syntax issues

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??

Tags:

4

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0 
    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;
$$;
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    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;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search