3 ms·
Wow, 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/do
by simonw 4y ago
Wow, 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".