Operator based on data type

From: Daniel McGreal <d(dot)j(dot)mcgreal(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Operator based on data type
Date: 2012-01-12 13:20:58
Message-ID: CACAnjQyUxKaJ13a+6-Ou9jo4P-T6+EUG9t-XkfcLL96w82a44w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message seiliki 2012-01-12 14:25:51 unnest array of row type
Previous Message eshishki 2012-01-12 13:08:56 Corrupted index, what do i do?