2013/2/16 Glenn Pierce <glennpierce@gmail.com>:
> Hi
>
> Does anyone know how one would
> select from a table with a hstore field treating the key of the hstore as
> case insensitive.
>
> ie
>
> SELECT id, lower(additional_info->'type') AS type FROM table
>
> I would like this to work even if if the store tyoe is
>
> 'Type' -> 'original'
As far as I can see from looking at the docs, it's not possible (I
could be wrong though).
> failing that is there a way to lowercase the keys and values of the hstore
> field of the entire table ?
You could create a function like this:
CREATE OR REPLACE FUNCTION hstore_to_lower(val HSTORE)
RETURNS HSTORE
LANGUAGE plpgsql
AS $function$
DECLARE
hkey TEXT;
BEGIN
FOR hkey IN
SELECT SKEYS(val)
LOOP
IF LOWER(hkey) != hkey THEN
val := val || (LOWER(hkey) || '=>' ||
LOWER((val->hkey::TEXT)))::HSTORE;
val := val - hkey;
END IF;
END LOOP;
RETURN val;
END;
$function$
No guarantee of suitability for a particular purpose or of it being
the optimal way of
doing this ;)
Note that any keys converted to lower case will overwrite existing
lower case keys.
HTH
Ian Barwick