CODEWITHSUNDEEP
postgresqlplpgsql
Anastasia Kar
Anastasia Kar

Reputation: 33

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead

I am trying to find the maximum concecutive duration dor each room per weekday. For example (15B,1,8,10) : the room_id 15B has the longest duration on Monday from 8:00 to 10:00. Start_time and end_time are time values and i want to convert them to integer. Weekday is varchar and i convert it to integer(0->sunday, 1->monday etc).

Here is my code

create or replace function iweekday(weekday  varchar(9))
returns int as $$
begin
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;
end;
$$ language plpgsql;

drop function fourpointnine();
create or replace function fourpointnine()
returns table (room varchar(7), iw int, st int, et 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, 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 as room, iweekday(c.weekday) AS iw, extract (epoch from c.start_time/3600) as st, extract (epoch from c.end_time/3600) as et
FROM   cte c
ORDER  BY 1, 2, st - et;


end;
$$ language plpgsql;

select * from  fourpointnine()

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 3 at SQL statement

Upvotes: 0

Views: 8623

Answers (1)

Thom Brown
Thom Brown

Reputation: 2039

This is because you have run queries in your function, but you haven't done anything with the result. You need to have a RETURN clause in there with the resultset, or specifically, RETURN QUERY.

See the PostgreSQL for returning a result from a function: https://www.postgresql.org/docs/12/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

For example:

CREATE OR REPLACE FUNCTION fourpointnine()
RETURNS TABLE (room varchar(7), iw int, st int, et int) AS $$
DECLARE iw INT;
BEGIN

RETURN 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 AS room, iweekday(c.weekday) AS iw, extract (epoch FROM c.start_time/3600) AS st, extract (epoch FROM c.end_time/3600) AS et
FROM cte c
ORDER BY 1, 2, st - et;


END;
$$ LANGUAGE plpgsql;

Upvotes: 0

Related Questions