> > SELECT name, to_char(date, 'mon') AS month, extract(month from date) AS > month_order, sum(amount) AS amount FROM invoices GROUP BY 1,2,3; > > and crosstabview command (per Daniel proposal) > > \crosstabview +name +month:month_order amount > > But if I don't need column header in human readable form, I can do > > \crosstabview +name +month_order amount > > What is solution of this use case with your proposal?? >
So it would just be SELECT name, to_char(date, 'mon') AS month, sum(amount) AS amount, extract(month from date) AS month_order FROM invoices GROUP BY 1,2,3 ORDER BY name \crosstabview name month amount month_order
Warning: :) Now I am subjective. The Daniel syntax "\crosstabview +name +month:month_order amount" looks more readable for me, because related things are near to self.
Note that I might also want to pass additional sort options, such as "ORDER BY name NULLS LAST", which the existing syntax doesn't allow. In the new syntax, such sort options could be trivially supported in both the server- and client-side sorts:
SELECT name, to_char(date, 'mon') AS month, extract(month from date) AS month_order, sum(amount) AS amount FROM invoices GROUP BY 1,2,3 ORDER BY name NULLS LAST \crosstabview name month amount month_order asc nulls last
I understand - if I compare these two syntaxes I and I am trying be objective, then I see
your: + respect SQL clauses ordering, allows pretty complex ORDER BY clause
- possible to fail on unexpected syntax errors
+/- more verbose
- allow only one client side sort
- less expressive
Daniel:
+ cannot to fail on syntax error
+ more compacts (not necessary to specify ORDER BY clauses)
+ allow to specify sort in both dimensions
+ more expressive (+colH is more expressive than colV colH col colH
- doesn't allow to complex order clauses in both dimensions
This is probably not an issue in this example, but it might well be in other cases. The +/-scol syntax is always going to be limited in what it can support.
the +/- syntax can be enhanced by additional attributes - this is only syntax (but then there is a risk of possible syntax errors)
> I agree so this syntax is pretty raw. But it is consistent with other psql > statements and there are not possible conflicts. > > What I mean? Your syntax is not unambiguous: \crosstabview [colV] [colH] > [colG1[,colG2...]] [sortCol [asc|desc]] - when I would to enter sort order > column, I have to enter one or more colG1,... or I have to enter explicitly > asc, desc keyword. >
That is resolved by the comma that precedes colG2, etc. isn't it?
but colG1 is optional. What if you miss any colGx ?