A lot of nice things can be done with postgresql’s hstore type.

They are a nice way to store flags, for example, and by providing an aggregate function to sum them up, one can easily compute complex unions of properties.

First we define an aggregate function to sum postgresql hstores, straightforward as this:

CREATE AGGREGATE sum(
  sfunc     = hs_concat,
  basetype  = hstore,
  stype     = hstore,
  initcond  = ''
);

Allows for beautiful constructs as

CREATE OR REPLACE FUNCTION array_to_hstore(text[]) RETURNS hstore AS $$
  DECLARE
    arr     text[];
    result  hstore;
  BEGIN
    SELECT INTO arr $1;

    SELECT INTO result sum(key=>1) FROM (
      select
        btrim(arr[idx.i]) as key
      from
        generate_series(1, array_upper(arr,1)) as idx(i)
    ) as dummy;

    RETURN result;

  END
$$ LANGUAGE plpgsql;

Now what can be done with that? For example we can turn comma separated lists into hstores:

mydb=> SELECT array_to_hstore(string_to_array('a, b, c, a', ','));
              array_to_hstore              
-------------------------------------------
 "a"=>"1", "b"=>"1", "c"=>"1"
(1 Zeile)