Getting the last element of a Postgres array, declaratively

Question

How to obtain the last element of the array in Postgres?

I need to do it declaratively as I want to use it as a ORDER BY criteria. I wouldn't want to create a special PGSQL function for it, the less changes to the database the better in this case.

In fact, what I want to do is to sort by the last word of a specific column containing multiple words. Changing the model is not an option here.

In other words, I want to push Ruby's sort_by {|x| x.split[-1]} into the database level. I can split a value into array of words with Postgres string_to_array or regexp_split_to_array functions, then how to get its last element?

Answer 1

Use array_upper():

SELECT array_upper(ARRAY[1,2,5,6], 1);

Answer 2

I guess you must use array_length() :

SELECT string_to_array('hi guys, welcome', ' ') AS arr INTO temparr;
SELECT * FROM temparr;
         arr
----------------------
 {hi,"guys,",welcome}

SELECT arr[array_length(arr,1)] FROM temparr;
   arr
---------
 welcome

To use this declaratively, (on the fly) you can create a little SQL function:

CREATE FUNCTION last_elem (text[]) RETURNS text AS $$
 SELECT $1[array_length($1,1)];
$$ LANGUAGE SQL;


 select last_elem(string_to_array('hi guys, welcome', ' '));
 last_elem
-----------
 welcome