{"id":1445,"date":"2018-10-16T18:28:56","date_gmt":"2018-10-16T16:28:56","guid":{"rendered":"http:\/\/www.netnea.com\/cms\/?p=1445"},"modified":"2018-10-16T22:47:03","modified_gmt":"2018-10-16T20:47:03","slug":"postgresql-create-a-sum-aggregate-for-text-columns","status":"publish","type":"post","link":"https:\/\/www.netnea.com\/cms\/2018\/10\/16\/postgresql-create-a-sum-aggregate-for-text-columns\/","title":{"rendered":"PostgreSQL: Create a sum() aggregate for text columns"},"content":{"rendered":"<p>Recently one of our clients wanted to aggregate text data in PostgreSQL using <a href=\"https:\/\/github.com\/pentaho\/mondrian\">Mondrian<\/a>. Without going too deep into Mondrian details, this requires an aggregate function that&#8217;s called sum() and accepts a single text argument. While stock PostgreSQL fulfills the first criteria, the function is defined for many data types but not text.<\/p>\n<p>Creating some test data with <a href=\"https:\/\/www.postgresql.org\/docs\/10\/static\/functions-srf.html\">generate_series<\/a>:<\/p>\n<pre>create table agg_test as (\r\n\u00a0 with s as (\r\n\u00a0\u00a0\u00a0 select generate_series(1, 5) as intcol\r\n\u00a0 )\r\n\u00a0 select\r\n\u00a0 intcol, 'key' || intcol as textcol\r\n\u00a0 from s\r\n);<\/pre>\n<pre>sumagg=# select * from agg_test ;\r\nintcol | textcol \r\n--------+---------\r\n1 | key1\r\n2 | key2\r\n3 | key3\r\n4 | key4\r\n5 | key5\r\n(5 rows)\r\n\r\n<\/pre>\n<p>Intended behaviour on an int colum:<\/p>\n<pre>\r\nsumagg=# select sum(intcol) from agg_test;\r\n sum \r\n-----\r\n  15\r\n\r\n<\/pre>\n<p>But text has no concept of summation:<\/p>\n<pre>sumagg=# select sum(textcol) from agg_test;\r\nERROR:  function sum(text) does not exist\r\nLINE 1: select sum(textcol) from agg_test;\r\n               ^\r\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.\r\n<\/pre>\n<p>Luckily in PostgreSQL it&#8217;s very easy to define custom aggregations. To make a sum() of some strings which is the same strings glued together with commas, we only need a helper functions that takes the both the already aggreagated strings (argument a) and the next string to append (argument b). We use <a href=\"https:\/\/www.postgresql.org\/docs\/10\/static\/functions-string.html\">concat_ws\u00a0<\/a>since it while handle NULLs the way we want:<\/p>\n<pre>\r\nCREATE OR REPLACE FUNCTION textsum_helper(a text, b text) RETURNS text AS $$\r\n        BEGIN\r\n                RETURN CASE WHEN a = '' THEN b ELSE concat_ws(', ', a, b) END;\r\n        END;\r\n$$ LANGUAGE plpgsql;\r\n<\/pre>\n<p>Using this function, the aggregate can now simply be easily defined:<\/p>\n<pre>\r\nCREATE AGGREGATE sum (text)\r\n(\r\n    sfunc = textsum_helper,\r\n    stype = text,\r\n    initcond = ''\r\n);\r\n\r\n<\/pre>\n<p>This finally allows us to sum up text:<\/p>\n<pre>\r\nsumagg=# select sum(textcol) from agg_test;                                                                                                                                                                                                                       sum              \r\n------------------------------\r\n key1, key2, key3, key4, key5\r\n(1 row)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Recently one of our clients wanted to aggregate text data in PostgreSQL using Mondrian. Without going too deep into Mondrian details, this requires an aggregate function that&#8217;s called sum() and accepts a single text argument. While stock PostgreSQL fulfills the first criteria, the function is defined for many data types but not text. Creating some [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44,5],"tags":[],"class_list":{"0":"post-1445","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-databases","7":"category-linux","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/posts\/1445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/comments?post=1445"}],"version-history":[{"count":11,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/posts\/1445\/revisions"}],"predecessor-version":[{"id":1457,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/posts\/1445\/revisions\/1457"}],"wp:attachment":[{"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/media?parent=1445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/categories?post=1445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.netnea.com\/cms\/wp-json\/wp\/v2\/tags?post=1445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}