Postgresql – Looping with array of values in PL/pgSQL

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.

If your use case is to retrieve all array elements of all rows, an index is not going to help with that. Postgres indexes can only hold a single index entry for a single table row. And a GIN index would not support the sort.

The best option I see would be a MATERIALIZED VIEW - which needs a refresh after any write operations on the underlying table that might influence the result. So probably only useful for read-only (or mostly) tables.

CREATE MATERIALIZED VIEW foo_elements AS
SELECT unnest(bar) AS u, id
FROM   foo
ORDER  BY u;