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

Question

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

Does this answer your question? [PostgreSQL: Query has no destination for result data](https://stackoverflow.com/questions/23946735/postgresql-query-has-no-destination-for-result-data) — zforgo, May 20 '20 at 15:22

Thom Brown · Answer 1 · 2020-05-20T15:39:28.473

0

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;

edited May 20 '20 at 15:39

answered May 20 '20 at 15:22

Thom Brown

1,869
4
11

But I am using the return table – Anastasia Kar May 20 '20 at 15:26
That clause just tells the function the type of the result, and it forms part of the function signature. The actual return needs to be within the body of the function. I've updated my answer with an example. – Thom Brown May 20 '20 at 15:40
1

You don't actually need PL/pgSQL for that. A simple `language sql` function would do as well – May 20 '20 at 15:41
This is also true, and a better way to do it. – Thom Brown May 20 '20 at 15:42
Thank you for your response! However, i get this error – Anastasia Kar May 20 '20 at 15:44
ERROR: structure of query does not match function result type DETAIL: Returned type integer does not match expected type character varying in column 1. CONTEXT: PL/pgSQL function fourpointnine() line 5 at RETURN QUERY SQL state: 42804 – Anastasia Kar May 20 '20 at 15:44
Yeah, that's fairly simple to fix, but it's even simpler to follow @a_horse_with_no_name's suggestion of using a plain SQL function. – Thom Brown May 20 '20 at 15:45

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

1 Answers1