4 ms·
Then try this query instead ? https://yaml2sql.netlify.app/play/crosstab/ https://yaml2sql.netlify.app/play/crosstab/ How do you think ?
by revskill 4y ago
Then try this query instead ? https://yaml2sql.netlify.app/play/crosstab/ https://yaml2sql.netlify.app/play/crosstab/
How do you think ?
- simonw 4y agoWow, I'd never seen that crosstab() function before! TIL. https://www.postgresql.org/docs/current/tablefunc.html#id-1.11.7.52.5.5 https://www.postgresql.org/docs/current/tablefunc.html#id-1.... This raises a problem with the YAML DSL though. Consider the following (truncated) example: main: select: - field: ct.* from: alias: ct fields: ... crosstab: - select: - extra_infos.problem_id - extra_infos.info_type - extra_infos.info_value from: extra_infos - select: - extra_infos.info_type distinct: true from: extra_infos Inside the "from:" area, those alias and fields and crosstab keys are all at the same level. But... "alias:" and "fields:" correspond to features of the from clause itself, whereas "crosstab:" is a special custom thing (a macro?) which ends up being compiled into a call to the identically-named crosstab() feature. So to understand the YAML, I need to understand both how the crosstab() feature in PostgreSQL works AND how that YAML DSL represents "crosstab:" calls using a different syntax. I looked up crosstab in the PostgreSQL manual but that information won't help me figure out the DSL. So I think I'd rather stick with regular SQL.
- revskill 4y agoMy goal is to build a composable and consistent DSL. It should have few keywords and structures as possible. Of course there's some "fixed keyword" like above crosstab (due to inconsistent SQL syntax i think). "alias" + "fields" helps you build "as (field1, field2,...)" kind of thing. "select" + "from" must form a normal "sql query".