Re: Case insensitive hstore.

2013/2/16 Glenn Pierce <glennpierce(at)gmail(dot)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