0

Possible Duplicate:
Running a CTE query in a loop using PL/pgSQL

(Cross-post from https://stackoverflow.com/q/12625914/1555778)

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;
$$;
0

0

Browse other questions tagged or ask your own question.