Re: How to unnest an array with element indexes - Mailing list pgsql-sql

From Pavel Stehule
Subject Re: How to unnest an array with element indexes
Date
Msg-id CAFj8pRDRzVNJcbqomgNNCOcriJe_BmuxPPny1fm1tOr7BzPqKQ@mail.gmail.com
Whole thread Raw
In response to Re: How to unnest an array with element indexes  (AlexK <alkuzo@gmail.com>)
Responses Re: How to unnest an array with element indexes
List pgsql-sql
Hello

Simple plpgsql function can helps

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;

postgres=# select * from unnest_rownum(ARRAY[1,1,2,3,4,1,2,7]);
 id | element
----+---------
  1 |       1
  2 |       1
  3 |       2
  4 |       3
  5 |       4
  6 |       1
  7 |       2
  8 |       7
(8 rows)

postgres=# select * from unnest_rownum(ARRAY['A','B','C']);
 id | element
----+---------
  1 | A
  2 | B
  3 | C
(3 rows)

Regards

Pavel



2014-02-19 20:57 GMT+01:00 AlexK <alkuzo@gmail.com>:
David,

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.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792774.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

pgsql-sql by date:

Previous
From: AlexK
Date:
Subject: Re: How to unnest an array with element indexes
Next
From: Tom Lane
Date:
Subject: Re: How to unnest an array with element indexes