Re: Case insensitive hstore.

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