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.. :/

score 2 · Accepted Answer · answered Jun 08 '14 at 11:34

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;

Relevant, the syntax of CASE you used help me to solve this, thank you very much. — YukiAsuna, Jun 08 '14 at 11:56

klin · Answer 2 · 2014-06-08T11:33:09.643

Your main problem is that it is no need to use CASE in a subquery. Your query should look like this pseudocode:

SELECT
    e.code_module, 
    e.groupe, 
    e.sem,
    CASE ...
    CASE ...
    e.salle
FROM ...

But first things first, some tips of using CASE. Instead of:

SELECT CASE
    WHEN jour = 1 THEN 'Monday'
    WHEN jour = 2 THEN 'Tuesday'
    WHEN jour = 3 THEN 'Wednesday'
    WHEN jour = 4 THEN 'Thursday'
    WHEN jour = 5 THEN 'Friday'
    END "JOUR"
FROM (select 2 as jour) x

use:

SELECT CASE jour
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    END "JOUR"
FROM (select 2 as jour) x

But you can do it in this simple way ('2014-01-05' is Sunday):

SELECT to_char('2014-01-05'::date+ jour, 'day')
FROM (select 2 as jour) x

Instead of the second CASE you can write:

SELECT (heured + 8)::text || 'h'
FROM (select 2 as heured) x

Now your base query looks like this:

SELECT 
    e.code_module, 
    e.groupe, 
    e.sem,
    to_char('2014-01-05'::date+ jour, 'day'),
    (heured + 8)::text || 'h',
    e.salle
FROM edt_sem e where e.sem = 1;

And your function:

CREATE or REPLACE function show_edt(paramSem numeric)
    RETURNS TABLE(module varchar, groupe varchar, week numeric, day text, hour text, room varchar) AS
$$
BEGIN
    RETURN QUERY 
        SELECT 
            e.code_module::varchar, 
            e.groupe::varchar, 
            e.sem::numeric,
            to_char('2014-01-05'::date+ jour, 'day'),
            (heured + 8)::text || 'h',
            e.salle::varchar
        FROM edt_sem e where e.sem = paramSem;

END;
$$ LANGUAGE plpgsql; 

select * from show_edt(1);

Explanations helps me a lot, thank you for that. Both your answer and Neto's answer below help me solved this. — YukiAsuna, Jun 08 '14 at 11:55

Multiple select case in a select query

2 Answers2