Re: [patch] Proposal for \crosstabview in psql

Tom Lane wrote:

> I do not think we want any client-side sorting in this feature at all,
> because the minute you have any such thing, you are going to have an
> absolutely never-ending stream of demands for more sorting features:
> multi column, numeric vs text, ASC vs DESC, locale-aware, etc etc etc.

It doesn't really do any client-side sorting, the rest of the thread might
refer to it like that by oversimplification, but if the command requests
a header to be sorted, a "backdoor-style" query of this form
is sent to the server, with PQexecParams():

SELECT n FROM (VALUES ($1,1),($2,2),($3,3)...) ) AS l(x,n)
ORDER BY x [DESC]
where the values to display in the header are bound to
$1,$2,.. and the type associated with these parameters is
the PQftype() of the field from which these values come.
Then the <n> values coming back ordered by <x> tell us
where to position the values corresponding to $1,$2... in the
sorted header.

There are some cases when this sort cannot work.
For example if the field is an anonymous type or a ROW().
Or if the field is POINT(x,y), because our "point" type
does not support order by.
I believe these are corner cases for this feature. In these
cases, psql just displays the error message that PQexecParams()
emits.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite