Обсуждение: Operator based on data type

Good day!

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.

On 12/01/2012 9:20 PM, Daniel McGreal wrote:

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;

Oooh, that's clever. Nice!

People say function overloading is no good ....

--
Craig Ringer

On Fri, Jan 13, 2012 at 5:27 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> Oooh, that's clever. Nice!
>
> People say function overloading is no good ....

They do??

I wrote a similar set of functions to simplify a particular piece of
UI code. Three functions called 'str2int'; one takes varchar and
returns int, one takes int and returns int, and one takes bigint and
returns bigint. I can pass anything the UI works with through these,
and I have a guarantee that it'll compare as integer. (The varchar one
isn't quite the same as casting to int; it swallows errors and returns
0, because that's what we want to achieve.) Function overloading is
awesome!

ChrisA