From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: UNNEST result order vs Array data
Date: Thu, 20 Jun 2013 06:34:17 -0700 (PDT)
Message-ID: <1371735257983-5760126.post@n5.nabble.com> (raw)
In-Reply-To: <1371731544016-5760107.post@n5.nabble.com>
References: <1371724857304-5760087.post@n5.nabble.com>
<51C2DD42.4000002@dalibo.com>
<1371726033207-5760092.post@n5.nabble.com>
<51C2F177.8020808@dalibo.com>
<1371731544016-5760107.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
gmb wrote
>>> The best, which you won't
>>> like, is to wait for 9.4 where unnest() will most likely have a WITH
>>> ORDINALITY option and you can sort on that.
>
> The fact that this type of thing is on the 9.4 roadmap indicates (to me,
> in any case) that there are problems with the UNNEST functionality in the
> current version (I'm running 9.2).
>
> Thanks Vik, I'll take a look at the implementation you suggested.
To recap:
unnest() returns its output in the same order as the input. Since an array
is ordered it will be returned in the same output order by unnest. However,
since unnest() only returns a single column (though possibly of a composite
type) it cannot provide the row number in the output thus in order to
maintain the same order elsewhere in the query it is necessary to use
"ROW_NUMBER() OVER (...)" on the output of the unnest() - and before joining
it with any other unnest calls or tables - before supplying it to the rest
of the query. The "WITH ORDINALITY" functionality proposed for 9.4 will
cause the unnest() [and other] function to output this additional column
along with the usual output. This is, I am pretty such, a usability
enhancement that makes easier something that can be done today using
CTE/WITH and/or sub-queries.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760126.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: UNNEST result order vs Array data
In-Reply-To: <1371735257983-5760126.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox