{"action":"created","issue":{"url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/100","labels_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/100/labels{/name}","comments_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/100/comments","events_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/100/events","html_url":"https://github.com/tomjaguarpaw/haskell-opaleye/issues/100","id":102365740,"number":100,"title":"How to groupBy the result of a function call (and maybe, how to use arbitrary PG functions) ","user":{"login":"dbp","id":569509,"avatar_url":"https://avatars.githubusercontent.com/u/569509?v=3","gravatar_id":"","url":"https://api.github.com/users/dbp","html_url":"https://github.com/dbp","followers_url":"https://api.github.com/users/dbp/followers","following_url":"https://api.github.com/users/dbp/following{/other_user}","gists_url":"https://api.github.com/users/dbp/gists{/gist_id}","starred_url":"https://api.github.com/users/dbp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dbp/subscriptions","organizations_url":"https://api.github.com/users/dbp/orgs","repos_url":"https://api.github.com/users/dbp/repos","events_url":"https://api.github.com/users/dbp/events{/privacy}","received_events_url":"https://api.github.com/users/dbp/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":14,"created_at":"2015-08-21T12:13:08Z","updated_at":"2015-09-01T18:39:14Z","closed_at":"2015-08-21T12:56:53Z","body":"Here's a simple query that I'd like to be able to write:\r\n\r\nselect sum(quantity * price_in_cents), to_char(updated_at, 'YYYY-MM') from line_items group by to_char(updated_at, 'YYYY-MM');\r\n\r\nI added two Column -> Column functions for `*` and `to_char` (qualified import names match those in the operators module):\r\n\r\n```\r\n(.*) :: Column T.PGInt4 -> Column T.PGInt4 -> Column T.PGInt4\r\n(.*) = C.binOp HPQ.OpMul\r\n\r\ngetYearMonth :: Column T.PGTimestamptz -> Column T.PGText\r\ngetYearMonth (C.Column d) = C.Column (HPQ.FunExpr \"to_char\" [d, HPQ.ConstExpr (HPQ.StringLit \"YYYY-MM\")])\r\n```\r\n\r\nAnd then I have a query:\r\n\r\n```\r\nrevenueByMonths :: Query (Column PGText, Column PGInt4)\r\nrevenueByMonths =\r\n  aggregate (p2 (groupBy, sum)) $ proc () ->\r\n    do (LineItem (_ :: Column PGInt4) upd q pr) <- queryAll -< ()\r\n       returnA -< (getYearMonth upd, q .* pr)\r\n```\r\n\r\nWhich produces SQL that doesn't work. The SQL generated is:\r\n\r\n```\r\nSELECT \"result0_2\" as \"result1_3\",\r\n       \"result1_2\" as \"result2_3\"\r\nFROM (SELECT *\r\n      FROM (SELECT to_char(\"updated_at1_1\",E'YYYY-MM') as \"result0_2\",\r\n                   SUM((\"quantity2_1\") * (\"price_in_cents3_1\")) as \"result1_2\"\r\n            FROM (SELECT *\r\n                  FROM (SELECT \"id\" as \"id0_1\",\r\n                               \"updated_at\" as \"updated_at1_1\",\r\n                               \"quantity\" as \"quantity2_1\",\r\n                               \"price_in_cents\" as \"price_in_cents3_1\"\r\n                        FROM \"line_items\" as \"T1\") as \"T1\") as \"T1\"\r\n            GROUP BY COALESCE (to_char(\"updated_at1_1\",E'YYYY-MM'))) as \"T1\") as \"T1\"\r\n```\r\n\r\nWhere the problem is the COALESCE (if it's removed, the query works as expected).\r\n\r\nIs there a way to do this that works? "},"comment":{"url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/comments/136823285","html_url":"https://github.com/tomjaguarpaw/haskell-opaleye/issues/100#issuecomment-136823285","issue_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/100","id":136823285,"user":{"login":"tomjaguarpaw","id":1951567,"avatar_url":"https://avatars.githubusercontent.com/u/1951567?v=3","gravatar_id":"","url":"https://api.github.com/users/tomjaguarpaw","html_url":"https://github.com/tomjaguarpaw","followers_url":"https://api.github.com/users/tomjaguarpaw/followers","following_url":"https://api.github.com/users/tomjaguarpaw/following{/other_user}","gists_url":"https://api.github.com/users/tomjaguarpaw/gists{/gist_id}","starred_url":"https://api.github.com/users/tomjaguarpaw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tomjaguarpaw/subscriptions","organizations_url":"https://api.github.com/users/tomjaguarpaw/orgs","repos_url":"https://api.github.com/users/tomjaguarpaw/repos","events_url":"https://api.github.com/users/tomjaguarpaw/events{/privacy}","received_events_url":"https://api.github.com/users/tomjaguarpaw/received_events","type":"User","site_admin":false},"created_at":"2015-09-01T18:39:14Z","updated_at":"2015-09-01T18:39:14Z","body":"@benkolera http://hackage.haskell.org/package/opaleye-0.4.1.0\r\n\r\nIf you ever need a release just file an issue."}}