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 $$
<<outer_scope>> DECLARE
  coords RECORD;
    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 >= '2011-02-13 11:55:11' 
        AND calltime <= '2012-02-13 01:02:21'
        AND (calltime::time >= '11:55:11' 
        OR calltime::time <= '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 >= '2011-02-13 11:00:00' 
      AND cal.stamp <= '2012-02-13 01:02:21' 
      AND (
        extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13 11:00:00'::timestamp) or
        extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13 01:02:21'::timestamp) 
    ORDER BY stamp ASC;

  • 2
    I do't see the need for a cursor+loop. Why not a plain join? Am I missing something?
    – wildplasser
    Commented Sep 27, 2012 at 18:03
  • @wildplasser well the lat/lon values in the first table -that contains coordinates and timestamps- are not the same as the lat/lons in the second table -since I created "grids" with top left and bottom right coordinates according to a certain criteria- but it just dawned upon me that I could join the two parts whenever the latitude/longitude values from the first table are between the top left and bottom right lat/lons of the second table -I will test for about 1.06 million lat/lons so plpgsql was the first thing that came to my mind but I suppose this makes it even better-
    – sm90901
    Commented Sep 27, 2012 at 18:18
  • 2
    Which would mean that you can rewrite it as a function returning rows, or even a view yielding rows, or even a plain query yielding rows.
    – wildplasser
    Commented Sep 27, 2012 at 18:28
  • 2
    In some cases, the query optimiser could choose to reshuffle terms between the (CTE) subquery and the "main" query. In this case, the subquery is a calendar table+an aggregation, so there will not be much opportunity for shuffling. I am curious about the resulting plan. Good luck!
    – wildplasser
    Commented Sep 27, 2012 at 18:36
  • 2
    I found time and gave it my best shot. Added to my answer. Commented Sep 28, 2012 at 0:04

2 Answers 2


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:

   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 >= v._from::time OR
                        t.calltime::time <= 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 >= v._from::time OR
       cal.stamp::time <= 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.

  • 2
    @sm90901: Fixed a typo: c.coordinates_id -> q.coordinates_id. I am assuming here, you want to identify coordinates in the result. Maybe not, then you don't need it. Maybe you even want to aggregate over all coordinates, then remove c.coordinates_id from the CTE, too. But your original query indicated otherwise ... Commented Sep 28, 2012 at 0:28
  • Took care of the problems I mentioned and added the coordinate_id's, my bad design decision caused it to run slow. Many thanks for all your help, I think I'll delve deeper into the concepts of databases and performance tuning as soon as this project of mine is wrapped up.
    – sm90901
    Commented Oct 4, 2012 at 19:51

Since anonymous code blocks are essentially void-returning functions (which take no parameters), you cannot return from them. Technically you can do the following:

AS (
-- define your columns here

DO $$
INSERT INTO tmp_return
SELECT --the rest of your query

SELECT * FROM tmp_return;

But in your case, there is nothing in your query that must involve a loop, you can easily transform the coords part into just another CTE or a subquery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.