Using CASE within a stored procedure Postgres-CodePudding

I am trying to use CASE WHEN within a stored procedure to return day name with a dow parameter and i get an error about an invalid syntax

CREATE OR REPLACE FUNCTION ReturnWeekName(weekno double precision)
RETURNS  varchar AS $$

DECLARE 
     weekname varchar;
BEGIN

    CASE 
        WHEN weekno = 0 THEN 'Sunday'
        WHEN weekno = 1 THEN 'Monday'
        WHEN weekno = 2 THEN 'Tuesday'
        WHEN weekno = 3 THEN 'Wednesday'
        WHEN weekno = 4 THEN 'Thursday'
        WHEN weekno = 5 THEN 'Friday'
        WHEN weekno = 6 THEN 'Saturday'
    END AS weekname

    END;
    $$
LANGUAGE plpgsql;

Error: 
*ERROR:  syntax error at or near "'Sunday'"
LINE 9:   WHEN weekno = 0 THEN 'Sunday'
                               ^
SQL state: 42601
Character: 158*

CodePudding user response：

You need to assign the result of the CASE expression to the variable:

CREATE OR REPLACE FUNCTION ReturnWeekName(weekno double precision)
RETURNS  varchar AS $$

DECLARE 
     weekname varchar;
BEGIN

    weekname := CASE 
        WHEN weekno = 0 THEN 'Sunday'
        WHEN weekno = 1 THEN 'Monday'
        WHEN weekno = 2 THEN 'Tuesday'
        WHEN weekno = 3 THEN 'Wednesday'
        WHEN weekno = 4 THEN 'Thursday'
        WHEN weekno = 5 THEN 'Friday'
        WHEN weekno = 6 THEN 'Saturday'
    END;
    return weekname;
END;
$$
LANGUAGE plpgsql;

Or a bit simpler using a SQL function:

CREATE OR REPLACE FUNCTION ReturnWeekName(weekno double precision)
RETURNS  varchar AS $$
  SELECT CASE 
        WHEN weekno = 0 THEN 'Sunday'
        WHEN weekno = 1 THEN 'Monday'
        WHEN weekno = 2 THEN 'Tuesday'
        WHEN weekno = 3 THEN 'Wednesday'
        WHEN weekno = 4 THEN 'Thursday'
        WHEN weekno = 5 THEN 'Friday'
        WHEN weekno = 6 THEN 'Saturday'
    END;
$$
LANGUAGE sql;