pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Vik Fearing <vik.fearing@dalibo.com>
To: gmb <gmbouwer@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: UNNEST result order vs Array data
Date: Thu, 20 Jun 2013 14:11:35 +0200
Message-ID: <51C2F177.8020808@dalibo.com> (raw)
In-Reply-To: <1371726033207-5760092.post@n5.nabble.com>
References: <1371724857304-5760087.post@n5.nabble.com>
	<51C2DD42.4000002@dalibo.com>
	<1371726033207-5760092.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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;



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

view thread (9+ messages)  latest in thread

Message-ID: <51C2F177.8020808@dalibo.com>
Permalink:  ../51C2F177.8020808@dalibo.com/
Also on:    postgresql.org/message-id/51C2F177.8020808@dalibo.com

reply

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: vik.fearing@dalibo.com, gmbouwer@gmail.com
  Subject: Re: UNNEST result order vs Array data
  In-Reply-To: <51C2F177.8020808@dalibo.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