Skip to content

Instantly share code, notes, and snippets.

@timothyklim
Last active October 13, 2015 19:30
Show Gist options
  • Save timothyklim/4245031 to your computer and use it in GitHub Desktop.
Save timothyklim/4245031 to your computer and use it in GitHub Desktop.
Aggregate hstore column in PostgreSQL 9.2 with PLV8
DROP FUNCTION IF EXISTS hstore_from_agg(hstores hstore[]);
CREATE FUNCTION hstore_from_agg(hstores hstore[]) RETURNS hstore AS
$$
var result = {}
hstores.forEach(function (sHash) {
sHash.split(/,\s*/).forEach(function(expression) {
var parsedValues = expression.match(/"(\d+)"=>"(\d+)"/),
key = parsedValues[1],
value = parseInt(parsedValues[2])
if (result[key] == undefined) {
result[key] = value
} else {
result[key] += value
}
})
})
var resultArray = []
for (var key in result) {
resultArray.push('"'+key+'"=>"'+result[key]+'"')
}
return resultArray.join(', ')
$$
LANGUAGE plv8 IMMUTABLE STRICT;
SELECT hstore_from_agg(array_agg(hstore_column)) from table_with_hstore
GROUP BY table_with_hstore.some_special_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment