Re: [patch] Proposal for \crosstabview in psql
| От | Pavel Stehule |
|---|---|
| Тема | Re: [patch] Proposal for \crosstabview in psql |
| Дата | |
| Msg-id | CAFj8pRCrSDfMBLx9gN=g9_yOELkqoy-bfmnFH4ZvdtmDNOsFag@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [patch] Proposal for \crosstabview in psql (Dean Rasheed <dean.a.rasheed@gmail.com>) |
| Ответы |
Re: [patch] Proposal for \crosstabview in psql
(Dean Rasheed <dean.a.rasheed@gmail.com>)
|
| Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: