I started off writing a question to this list, but in so doing I thought of a solution! :) So, I'll try and record the result, in case anyone else finds themselves in this unfortunate situation or has suggestions for improvements (especially any regarding query performance).
My system generates queries that are then sent to PostgreSQL via JDBC based on criteria that are passed to me from an external system. My problem was that I cannot know, ahead of time, whether a particular criterion operates on a data type that's an array. For example, I needed a generic solution that incorporates both of the following:
select * from unnest(array[1,2,3]) as r(e) where 1 = r.e; -- i.e. data type is not an array select e from (select array[1,2,3]) as a(e) where 2 = any(e); -- i.e. data type is an array
I have ended up writing a function (more accurately, two functions) which always convert a value into an array, either by returning the array immediately or by nesting it inside one so that all queries can use the second form, above.
CREATE OR REPLACE FUNCTION make_array(anyarray) RETURNS anyarray AS 'select $1' LANGUAGE sql IMMUTABLE COST 1; CREATE OR REPLACE FUNCTION make_array(anynonarray) RETURNS anyarray AS 'select array[$1]' LANGUAGE sql IMMUTABLE COST 1;
Dan.
P.S. Thanks for the 'anynonarray' type, it made my day.
