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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general