Re: [patch] Proposal for \crosstabview in psql

> >
> > 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
>