Last active
October 13, 2015 19:30
-
-
Save timothyklim/4245031 to your computer and use it in GitHub Desktop.
Aggregate hstore column in PostgreSQL 9.2 with PLV8
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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