3 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link
  • 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 @kgrittnin 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.
  • 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.
  • 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.
2 cleanup
source | link

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

Rewrite as single SQL query:

I would think youYou probably don't need plpgsql or loops at all. Consider this plain SQL query instead:

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

Rewrite as single SQL query:

I would think you don't need plpgsql or loops at all. Consider this plain SQL query instead:

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:

    Post Migrated Here from stackoverflow.com
1
source | link

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. I also suspect the whole operation can be simplified:

Rewrite as single SQL query:

I would think you 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.