Find the longest concecutive duration of each room_id per weekday

Question

I am trying to find the longest concecutive duration of each room_id per weekday. I want to convert the weekday to its integer representation (0 for Sunday, 1 for Monday etc).

What I have so far:

create or replace function iweekday(weekday  varchar(9))
returns int as $$
DECLARE iw INT;
begin
select  iw= CASE weekday
                        WHEN 'Monday' THEN 1
                        WHEN 'Tuesday' THEN 2
                        WHEN 'Wednesday' THEN 3
                        WHEN 'Thursday' THEN 4
                        WHEN 'Friday' THEN 5
                        WHEN 'Saturday' THEN 6  
                        WHEN 'Sunday' THEN 0
                    END;
end;
$$ language plpgsql;


create or replace function fourpointnine()
returns table (room_id character(7), iw int, start_time int,end_time int) as $$
DECLARE iw INT;
begin
with recursive cte as(
    select  l.room_id, l.weekday, l.start_time, l.end_time
    from "learningactivity" l
    union all 
    select l.room_id, l.weekday, cte.start_time, l.end_time
    from cte join "learningactivity" l on l.room_id=cte.room_id and l.weekday=cte.weekday and cte.end_time=l.start_time
),  cte2 as(
    select *,  row_number() over (partition by cte.room_id  order by EXTRACT(EPOCH FROM cte.end_time - cte.start_time)/3600 desc ) as rn
    from cte
)


select distinct cte2.room_id, iweekday(cte2.weekday), cte2.start_time, cte2.end_time
from cte2
where rn=1
group by cte2.room_id, iweekday(cte2.weekday), cte2.start_time, cte2.end_time;
end;
$$ language plpgsql;

But I get this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function iweekday(character varying) line 4 at SQL statement

How to do it properly?

If you need to return the scalar value from your function use RETURN statement, not SELECT into variable.. — Akina, May 19 '20 at 13:30

Erwin Brandstetter · Answer 1 · 2020-05-19T16:02:44.707

The immediate error could be fixed with RETURN QUERY. See:

How to return result of a SELECT inside a function in PostgreSQL?

But don't bother, the PL/pgSQL function is overkill to begin with.

Your helper function is much simpler and cheaper this way:

CREATE OR REPLACE FUNCTION iweekday(weekday text)
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT CASE weekday
         WHEN 'Monday'    THEN 1
         WHEN 'Tuesday'   THEN 2
         WHEN 'Wednesday' THEN 3
         WHEN 'Thursday'  THEN 4
         WHEN 'Friday'    THEN 5
         WHEN 'Saturday'  THEN 6  
         WHEN 'Sunday'    THEN 0
        END
$func$;

(PARALLEL SAFE only for Postgres 9.6 or later.)

Most importantly, it can be inlined. Related:

Difference between language sql and language plpgsql in PostgreSQL functions

And your main function can be replaced with this query:

WITH RECURSIVE cte AS (
   SELECT l.room_id, l.weekday, l.start_time, l.end_time
   FROM   learningactivity l

   UNION ALL 
   SELECT l.room_id, l.weekday, c.start_time, l.end_time
   FROM   cte c
   JOIN   learningactivity l ON l.room_id = c.room_id
                            AND l.weekday = c.weekday
                            AND l.start_time = c.end_time
  )
SELECT DISTINCT ON (1, 2)
       c.room_id, iweekday(c.weekday) AS iw, c.start_time, c.end_time
FROM   cte c
ORDER  BY 1, 2, c.start_time - c.end_time;

If you insist on a function:

CREATE OR REPLACE FUNCTION fourpointnine()
  RETURNS TABLE (room_id text, iw int, start_time int, end_time int)
  LANGUAGE sql AS
$func$
    -- paste query from above
$func$;

db<>fiddle here

Select longest continuous sequence

Aside: never use the data type character(N). See:

Any downsides of using data type "text" for storing strings?

Find the longest concecutive duration of each room_id per weekday

1 Answers1