Multiple select case in a select query

Question

I'm beginning in postgres and I have an issue regarding multiple select case in a select query.

bd_david=> select * from edt_sem;
 code_module |  groupe  | week | day  | hour   | room 
-------------+----------+------+-----+--------+-------
 M3106       | INFOFCAS |   1  |    1 |      1 | O104
 M3105       | INFOFCAS |   1  |    1 |      5 | O104
 M3106       | INFOFCAS |   2  |    1 |      1 | O104
(3 rows)

What I want to do is to create a function that only show rows of a certain week and for that I am using this function :

CREATE or REPLACE function show_edt(IN paramSem numeric)
    RETURNS TABLE(module varchar, groupe varchar, week numeric, day text, hour text, room varchar) AS
$$
BEGIN
    RETURN QUERY 
         SELECT e.code_module, 
                e.groupe, 
                e.sem, 
                (SELECT CASE
                WHEN e.jour = 1 THEN 'Monday'
                WHEN e.jour = 2 THEN 'Tuesday'
                WHEN e.jour = 3 THEN 'Wednesday'
                WHEN e.jour = 4 THEN 'Thursday'
                WHEN e.jour = 5 THEN 'Friday'
                END "JOUR"
                FROM edt_sem WHERE edt_sem.sem = paramSem [ LIMIT 1... ? Don't know ] ),
                (SELECT CASE
                WHEN heured = 1 THEN '9h'
                WHEN heured = 2 THEN '10h'
                WHEN heured = 3 THEN '11h'
                WHEN heured = 4 THEN '12h'
                WHEN heured = 5 THEN '14h'
                WHEN heured = 6 THEN '15h'
                WHEN heured = 7 THEN '16h'
                WHEN heured = 8 THEN '17h'
                END "HEURE"
                FROM edt_sem WHERE edt_sem.sem = paramSem [ LIMIT 2 ?? don't know.. ]),
                e.salle
                FROM edt_sem e where e.sem = paramSem;

END;
$$ LANGUAGE plpgsql;

It works well for ONE row :

select * from afficher_edt(2);
 module |  groupe  | week    | day   | hour  | room 
--------+----------+---------+-------+-------+-------
 M3106  | INFOFCAS |       2 | Monday| 9h    | O104
(1 row)

But I have issue for more than one row :

bd_david=> select * from afficher_edt(1);
 module |  groupe  | week    | day   | hour  | room 
--------+----------+---------+-------+-------+-------
 M3106  | INFOFCAS |       1 | Lundi | 9h    | O104
 M3105  | INFOFCAS |       1 | Lundi | 9h /!\| O104

/!\ It should have been displayed 14h instead of 9h

EDIT : The error message :

bd_david=> select * from show_edt(1);
ERROR:  more than one row returned by a subquery used as an expression
CONTEXT:  PL/pgSQL function afficher_edt(numeric) line 3 at RETURN QUERY

Well I get more or less why I got this error : it's because there are two rows fetched by my select case for different hours but I don't know how to get rid of it.. :/

Answer 1

CREATE or REPLACE function show_edt(IN paramSem numeric)
RETURNS TABLE (
    module varchar, groupe varchar, week numeric, 
    day text, hour text, room varchar
) AS $$
BEGIN
    RETURN QUERY 
        SELECT
            e.code_module, 
            e.groupe, 
            e.sem, 
            CASE e.jour
                WHEN 1 THEN 'Monday'
                WHEN 2 THEN 'Tuesday'
                WHEN 3 THEN 'Wednesday'
                WHEN 4 THEN 'Thursday'
                WHEN 5 THEN 'Friday'
            END as "JOUR",
            CASE heured
                WHEN 1 THEN '9h'
                WHEN 2 THEN '10h'
                WHEN 3 THEN '11h'
                WHEN 4 THEN '12h'
                WHEN 5 THEN '14h'
                WHEN 6 THEN '15h'
                WHEN 7 THEN '16h'
                WHEN 8 THEN '17h'
            END as "HEURE"
            e.salle
        FROM edt_sem e
        where e.sem = paramSem;
END;
$$ LANGUAGE plpgsql;

You don't need plpgsql for this. Just sql

CREATE or REPLACE function show_edt(IN paramSem numeric)
RETURNS TABLE (
    module varchar, groupe varchar, week numeric, 
    day text, hour text, room varchar
) AS $$
    SELECT
        e.code_module, 
        e.groupe, 
        e.sem, 
        CASE e.jour
            WHEN 1 THEN 'Monday'
            WHEN 2 THEN 'Tuesday'
            WHEN 3 THEN 'Wednesday'
            WHEN 4 THEN 'Thursday'
            WHEN 5 THEN 'Friday'
        END as "JOUR",
        CASE heured
            WHEN 1 THEN '9h'
            WHEN 2 THEN '10h'
            WHEN 3 THEN '11h'
            WHEN 4 THEN '12h'
            WHEN 5 THEN '14h'
            WHEN 6 THEN '15h'
            WHEN 7 THEN '16h'
            WHEN 8 THEN '17h'
        END as "HEURE"
        e.salle
    FROM edt_sem e
    where e.sem = paramSem;
$$ LANGUAGE sql;