From: | Daniel McGreal <daniel(dot)mcgreal(at)redbite(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Operator based on data type |
Date: | 2012-01-12 15:36:14 |
Message-ID: | CACAnjQzDyJixm0PCaJeW_Q6bQmC5oVVJuEuCFyLd3Q8c-FUQbg@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.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-01-12 17:11:47 | Re: unnest array of row type |
Previous Message | Pavel Stehule | 2012-01-12 14:47:55 | Re: unnest array of row type |