Running a CTE query in a loop using PL/pgSQL I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude coordinates of grids, I pass the top left and bottom right latitude / longitude values into my CTE in order to display the amount of requests (hourly) made within those coordinates for given two timestamps-. However, I cannot display the results of my CTE and I get the following error message:

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 "inline_code_block" line 6 at SQL statement


What should I change here in order to make the entire query work as needed? My code is as below:

DO $$
&lt;&lt;outer_scope&gt;&gt; DECLARE
  coords RECORD;
BEGIN
    FOR coords IN SELECT topleftlat, topleftlon, bottomrightlat, bottomrightlon FROM coordinates LOOP
        WITH cal AS (
        SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
                   '2012-04-01 05:00:00'::timestamp , 
                   '1 hour'::interval) AS stamp
    ),
    qqq AS (
      SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
      FROM mytable
      WHERE calltime &gt;= '2011-02-13 11:55:11' 
        AND calltime &lt;= '2012-02-13 01:02:21'
        AND (calltime::time &gt;= '11:55:11' 
        OR calltime::time &lt;= '01:02:21')
        AND lat BETWEEN coords.bottomrightlat AND coords.topleftlat
        AND lon BETWEEN coords.topleftlon AND coords.bottomrightlon
     GROUP BY date_trunc('hour', calltime)
    )
    SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
    FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
    WHERE cal.stamp &gt;= '2011-02-13 11:00:00' 
      AND cal.stamp &lt;= '2012-02-13 01:02:21' 
      AND (
        extract ('hour' from cal.stamp) &gt;= extract ('hour' from '2011-02-13 11:00:00'::timestamp) or
        extract ('hour' from cal.stamp) &lt;= extract ('hour' from '2012-02-13 01:02:21'::timestamp) 
      )
    ORDER BY stamp ASC;

    END LOOP;
END;
$$;
The DO command has no facility to actually return data (except with RAISE, or you could write to a (temp) table .. ).

You need to create a PL/pgSQL function that can define a return type with RETURNS and call it. 

You could return the result with RETURN QUERY EXECUTE. But I suspect the whole operation can be simplified ...

Rewrite as single SQL query

You probably don't need plpgsql or loops at all. Consider this plain SQL query instead:

WITH v AS (
   SELECT '2011-02-13 11:55:11'::timestamp AS _from -- provide times once
         ,'2012-02-13 01:02:21'::timestamp AS _to
   )
, q AS (
   SELECT c.coordinates_id
        , date_trunc('hour', t.calltime) AS stamp
        , count(*) AS zcount
   FROM   v
   JOIN   mytable t ON  t.calltime BETWEEN v._from AND v._to
                   AND (t.calltime::time &gt;= v._from::time OR
                        t.calltime::time &lt;= v._to::time)
   JOIN   coordinates c ON (t.lat, t.lon) 
                   BETWEEN (c.bottomrightlat, c.topleftlon)
                       AND (c.topleftlat, c.bottomrightlon)
   GROUP  BY c.coordinates_id, date_trunc('hour', t.calltime)
   )
, cal AS (
   SELECT generate_series(GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
                        , LEAST('2012-04-01 05:00:00'::timestamp, v._to)
                        , '1 hour'::interval) AS stamp
   FROM v
   )
SELECT q.coordinates_id, cal.stamp, COALESCE (q.zcount, 0) AS zcount
FROM   v, cal
LEFT   JOIN q USING (stamp)
WHERE (cal.stamp::time &gt;= v._from::time OR
       cal.stamp::time &lt;= v._to::time)
ORDER  BY q.coordinates_id, stamp;



Instead of looping through rows in table coordinates, join to the CTE and produce the whole result in one go.
As you aggregate per row of coordinates we need the primary key of this table (or any other unique set of columns) I assume a pk named coordinates_id.
I added the CTE v (for "values") on top to provide _from and _to timestamps once only.
I use _from and _to to limit the time range of the calender right away, instead of adding WHERE clauses to trim the surplus in the final SELECT.

GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
LEAST('2012-04-01 05:00:00'::timestamp, v._to)

I use "ad-hoc rows" like demonstrated in this related answer by @kgrittn for a much simpler JOIN condition:

     ON (t.lat, t.lon) 
BETWEEN (c.bottomrightlat, c.topleftlon)
    AND (c.topleftlat, c.bottomrightlon)

I cast to time (::time) instead of using extract ('hour' ..), because it's simpler and faster.


I am not 100 % sure this is exactly what you are after, but it should be very close.