Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
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 >= '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;

    END LOOP;
END;
$$;
share|improve this question

migrated from stackoverflow.com Sep 28 '12 at 2:27

This question came from our site for professional and enthusiast programmers.

2  
I do't see the need for a cursor+loop. Why not a plain join? Am I missing something? –  wildplasser Sep 27 '12 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 Sep 27 '12 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 Sep 27 '12 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 Sep 27 '12 at 18:36
2  
I found time and gave it my best shot. Added to my answer. –  Erwin Brandstetter Sep 28 '12 at 0:04

2 Answers 2

up vote 3 down vote accepted

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 >= 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.

share|improve this answer
    
Very detailed, thanks a lot. –  sm90901 Sep 28 '12 at 0:23
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 ... –  Erwin Brandstetter Sep 28 '12 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 Oct 4 '12 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:

CREATE TEMPORARY TABLE tmp_return
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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