Use specific array column in the where clause Having a function that returns an array and in a query we use the function to return results such as:

{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)","DEPARTURE DATE",29/01/2016,2,"ON THE ROAD"}


How can we add a WHERE clause that will filter by the 5th column of the array searching for the ON THE ROAD values only?

Is there a way to reference array[4]?

Tried with 

where trip_status(tlid) &lt;@ array['AT CHECKPOINT']::varchar


with trip_status() being the function that returns the array but am getting the following error:


Query failed: ERROR: operator does not exist: character varying[] &lt;@ character varying 
LINE 6: ...ere trip_closed(tlid)=false and trip_status(tlid) &lt;@ array[ ... ^ 
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.



Does something like where trip_status(tlid)[4] = 'AT CHECKPOINT' exist?
You put the cast outside the array constructor - use ::varchar[] instead.  The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle.

But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to use those in a WHERE clause, for example.  Then construct your array directly before returning it to whatever that needs it.