Message-ID: <51C2F177.8020808@dalibo.com>
Date: Thu, 20 Jun 2013 14:11:35 +0200
From: Vik Fearing <vik.fearing@dalibo.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
To: gmb <gmbouwer@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: UNNEST result order vs Array data
References: <1371724857304-5760087.post@n5.nabble.com>
 <51C2DD42.4000002@dalibo.com> <1371726033207-5760092.post@n5.nabble.com>
In-Reply-To: <1371726033207-5760092.post@n5.nabble.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 06/20/2013 01:00 PM, gmb wrote:
> Can you please give me an example of how the order is specified?
> I want the result of the UNNEST to be in the order of the array field
> E.g.
> SELECT UNNEST ( ARRAY[ 'abc' , 'ggh' , '12aa' , '444f' ] );
> Should always return:
>
>  unnest
> --------
>  abc
>  ggh
>  12aa
>  444f
>
> How should the ORDER BY be implemented in the syntax?

There are two ways I can think of right now.  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 other is to make your
own unnest function that will return the values plus the position.  That
would look something like this:

CREATE OR REPLACE FUNCTION unnest_with_ordinality(anyarray, OUT value
anyelement, OUT ordinality integer)
  RETURNS SETOF record AS
$$
SELECT $1[i], i FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$$
LANGUAGE sql IMMUTABLE;

and then

select value from unnest_with_ordinality(ARRAY[ 'abc' , 'ggh' , '12aa' ,
'444f']) order by ordinality;


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