honeysql 2021-09-09 | Slack Archive

Hi chat, how would you add a frame clause on window definitions using honey? i.e.

SELECT ...
  sum(amount) over (PARTITION BY fruit ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM fruit_sale

Noah Bogart21:01:58

#Also sent to the channel

i've actually written support for the frame clause recently. if there's still interest, I can open a PR for it

Noah Bogart21:01:33

sadly, it's fairly lengthy given how complex it is, but it works well for us

seancorfield04:09:24

@achan961117 That's more specific than the :partition-by syntax that HoneySQL supports right now. What DB is that for?

seancorfield04:09:04

Could you open an issue on GitHub with details and a link to the DB's docs for PARTITION BY and I'll see what I can do next time I'm working on OSS (in a week or two, I expect).

pinealan04:09:26

it’s for postgres, I realised it’s pretty specific usecases so I’m reading up the docs on register-clause! now

pinealan04:09:37

here’s the postgres docs for reference https://www.postgresql.org/docs/11/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

seancorfield04:09:46

Put it in an issue 🙂

pinealan04:09:58

sure will do 👌

seancorfield04:09:10

And, yeah, I f'ing h8t PostgreSQL 🙂

😟 4

seancorfield04:09:46

It's like the absolute opposite of Clojure in a database 😛

cyppan11:09:27

Is there a way to deactivate the transform of “-” into “_” in table names? BigQuery uses dashes in project names, and the table name is in the form projectName.datasetName.tableName

(sql/format (sql-h/create-table :test-dash))
=> ["CREATE TABLE test_dash"]

V12:09:44

(sql/format (sql-h/create-table :test-dash) {:quoted true})
=> ["CREATE TABLE \"test-dash\""]

cyppan12:09:06

Thank you I’ve tried that but there is another problem, I need two dots in my table name, like project-dashed.dataset.table I’ve tried

(sql/format (sql-h/create-table :project-dashed.dataset/table) {:quoted true})
=> ["CREATE TABLE \"project_dashed.dataset\".\"table\""]

(sql/format (sql-h/create-table :project-dashed.dataset.table) {:quoted true})
=> ["CREATE TABLE \"project-dashed\".\"dataset\""]
;; it works but the table name disapeared

(sql/format (sql-h/create-table "project-dashed.dataset.table") {:quoted true})
Execution error (NullPointerException) at honey.sql/namespace (sql.cljc:154).
null

seancorfield17:09:17

@U0CL38MU1 Looks like it is actually two bugs compounded here. After I extended :create-table's syntax, it no longer processes strings correctly for the table name, which was supposed to be the way to handle this, but there's also a bug in format-entity that doesn't handle multi-segment names in string properly. https://github.com/seancorfield/honeysql/issues/353

seancorfield17:09:55

That's fixed on develop now if you want to try it out via git deps but I need to do a pass over the documentation to make sure the behavior is accurately reflected there (and probably to add notes about using strings for complex, qualified entity names).

cyppan18:09:30

awesome thank you @U04V70XH6 I’ll try that

cyppan18:09:50

I was indeed surprised the string version didn’t work

V12:09:58

Might solve your issue

Noah Bogart21:01:58

replied to a thread:Hi chat, how would you add a `frame clause` on window definitions using honey? i.e. SELECT ... sum(amount) over (PARTITION BY fruit ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM fruit_sale

i've actually written support for the frame clause recently. if there's still interest, I can open a PR for it

2021-09-09

Channels