Search Postgresql Archives

Re: Case insensitive hstore.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2013/2/16 Glenn Pierce <glennpierce@xxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux