Hi Looking at this patch, I have mixed feelings about it. On the one hand > I really like the look of the output, and I can see that the non-fixed > nature of the output columns makes this hard to achieve server-side. > > But on the other hand, this seems to be going way beyond the normal > level of result formatting that something like \x does, and I find the > syntax for sorting particularly ugly. I can understand the need to > sort the colH values, but it seems to me that the result rows should > just be returned in the order the server returns them -- i.e., I don't > think we should allow sorting colV values client-side, overriding a > server-side ORDER BY clause in the query. >
This feature has zero relation with \x option, and any link to this option is confusing. This is important, elsewhere we are on start again, where I did long discuss with Daniel about the name, when I blocked the name "rotate". > Client-side sorting makes me uneasy in general, and I think it should > be restricted to just sorting the columns that appear in the output > (the colH values). This would also allow the syntax to be simplified: > > \crosstabview [colV] [colH] [colG1[,colG2...]] [sortCol [asc|desc]] > The sorting on client side is necessary - minimally in one direction, because you cannot to create perfect sorting for both dimensions. Possibility to order in second dimension is just pretty comfortable - because you don't need to think two steps forward - when you create SQL query. I have a basic use case that should be supported well, and it is supported well by last version of this patch. The evaluation of syntax is subjective. We can compare Daniel's syntax and your proposal. The use case: I have a table with the invoices with attributes (date, name and amount). I would to take a report of amounts across months and customers. Horizontal dimension is month (name), vertical dimension is name of customers. I need sorting of months in semantic order and customers in alphabet order. So my query is: 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?? 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. Regards Pavel > > Overall, I like the feature, but I'm not convinced that it's ready in > its current form. > > For the future (not in this first version of the patch), since the > transformation is more than just a \x-type formatting of the query > results, a nice-to-have feature would be a way to save the results > somewhere -- say by making it play nicely with \g or \copy somehow, > but I admit that I don't know exactly how that would work. > > Regards, > Dean >