Re: UNNEST result order vs Array data - Mailing list pgsql-sql

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;