create or replace function unnest_rownum(anyarray) returns table (id int, element anyelement) as $$ begin id := 1; foreach element in array $1 loop return next; id := id + 1; end loop; return; end $$ language plpgsql;
The array stores a time series of values for consecutive days. All I need is take an array such as ARRAY[1.1,1.2] and return to the client the following
series_start_date + (array_index-1), array_value
Based on what you are saying, the following should do it:
with pivoted_array AS( select unnest(ARRAY[1.1,1.2]) ), indexed_array AS( select row_number()OVER() AS element_index, unnest as element_value from pivoted_array) SELECT (DATE '2014-02-19' + INTERVAL '1d'*(element_index-1)) AS series_date, element_value AS series_value FROM indexed_array
Can you confirm that this behavior is guaranteed and documented. I could not find it in the docs.