postgres subquery w/ derived column

Question

The following query won't work, but it should be clear what I'm trying to do: split the value of 't' on space and use the last element in that array in the subquery (as it will match tl). Any ideas how to do this? Thanks!

SELECT t, y, "type",
regexp_split_to_array(t, ' ') as t_array, sum(dr), (

select uz from f.tfa where tl = t_array[-1]

)
as uz,
sc
FROM padres.yd_fld
WHERE y = 2010 AND pos <> 0
GROUP BY t, y, "type", sc;

Answer 1

I don't think you can you a negative index to reference an array element in postgresql (documentation) ?

test=# select t from 
      (select regexp_split_to_array('hi man bye', ' ') as t) as x;       
--------------
 {hi,man,bye}
(1 row)

test=# select t[1] from 
      (select regexp_split_to_array('hi man bye', ' ') as t) as x;
 t
----
 hi
(1 row)

test=# select t[-1] from 
      (select regexp_split_to_array('hi man bye', ' ') as t) as x;
 t
---

(1 row)

I find strange that it doesn't give an error, though...

The correct way would be:

test=# select t[array_length(t,1)] from 
  (select regexp_split_to_array('hi man bye', ' ') as t) as x;
  t
-----
 bye

Also, see here:

http://stackoverflow.com/questions/2949881/getting-the-last-element-of-a-postgres-array-declaratively/2950441#2950441