Date: Wed, 19 Feb 2014 11:32:23 -0800 (PST)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1392838343105-5792771.post@n5.nabble.com>
In-Reply-To: <1392837956043-5792770.post@n5.nabble.com>
References: <1392837956043-5792770.post@n5.nabble.com>
Subject: Re: How to unnest an array with element indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

AlexK wrote
> Given an array such as ARRAY[1.1,1.2], I need to select both values and
> indexes, as follows:
> 
> 1;1.1
> 2;1.2
> 
> The following query does what I want for a simple example:
> 
> with pivoted_array AS(
> select unnest(ARRAY[1.1,1.2])
> )
> select ROW_NUMBER() OVER() AS element_index, unnest as element_value
> from pivoted_array
> 
> Is ROW_NUMBER() OVER() guaranteed to always return array's index? If not,
> how should I predictably/deterministically do it?

9.4 will provide for this capability directly.  For earlier releases as long
as the next and only thing you do after unnesting the array is apply the
window function the order will be consistent - the rows will be seen by the
window in array order.  You must not perform any other joins until the row
numbers have been assigned.  It is best to use a pair of CTE/WITH queries to
accomplish this and then use the result of the second CTE in the main query.

If your need is much more complicated than the simple example provided you
may wish to give something more close to your actual need for some to opine
on.

David J.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-unnest-an-array-with-element-indexes-tp5792770p5792771.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