3

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) <@ 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[] <@ character varying 
LINE 6: ...ere trip_closed(tlid)=false and trip_status(tlid) <@ 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?

7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.