> > > > 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 ? Regards Pavel > > Regards, > Dean >
