Thread: [patch] Proposal for \rotate in psql

 Hi,

This is a reboot of my previous proposal for pivoting results in psql,
with a new patch that generalizes the idea further through a command
now named \rotate, and some examples.

So the concept is: having an existing query in the query buffer,
the user can specify two column numbers C1 and C2 (by default the 1st
and 2nd) as an argument to a \rotate command.

The query results are then displayed in a 2D grid such that each tuple
(vx, vy, va, vb,...) is shown as |va vb...| in a cell at coordinates (vx,vy).
The values vx,xy come from columns C1,C2 respectively and are
represented in the output as an horizontal and a vertical header.

A cell may hold several columns from several rows, growing horizontally and
vertically (\n inside the cell) if necessary to show all results.

The examples below should be read with a monospaced font as in psql,
otherwise they will look pretty bad.

1. Example with only 2 columns, querying login/group membership from the
catalog.
   Query:

SELECT r.rolname as username,r1.rolname as groupname
  FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
  ON (m.member = r.oid)
  LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
  WHERE r.rolcanlogin
  ORDER BY 1

Sample results:
  username  | groupname
------------+-----------
 daniel     | mailusers
 drupal     |
 dv        | admin
 dv        | common
 extc        | readonly
 extu        |
 foobar     |
 joel        |
 mailreader | readonly
 manitou    | mailusers
 manitou    | admin
 postgres   |
 u1        | common
 u2        | mailusers
 zaz        | mailusers


Applying \rotate gives:
           Rotated query results
  username  | admin | common | mailusers | readonly
------------+-------+--------+-----------+----------
 daniel     |        |         | X     |
 drupal     |        |         |         |
 dv        | X     | X      |         |
 extc        |        |         |         | X
 extu        |        |         |         |
 foobar     |        |         |         |
 joel        |        |         |         |
 mailreader |        |         |         | X
 manitou    | X     |         | X     |
 postgres   |        |         |         |
 u1        |        | X      |         |
 u2        |        |         | X     |
 zaz        |        |         | X     |

The 'X' inside cells is automatically added as there are only
2 columns. If there was a 3rd column, the content of that column would
be displayed instead (as in the next example).

What's good in that \rotate display compared to the classic output is that
it's more apparent, visually speaking, that such user belongs or not to such
group or another.

2. Example with a unicode checkmark added as 3rd column, and
   unicode linestyle and borders (to be seen with a mono-spaced font):

SELECT r.rolname as username,r1.rolname as groupname, chr(10003)
  FROM pg_catalog.pg_roles r LEFT JOIN pg_catalog.pg_auth_members m
  ON (m.member = r.oid)
  LEFT JOIN pg_roles r1 ON (m.roleid=r1.oid)
  WHERE r.rolcanlogin
  ORDER BY 1

        Rotated query results
┌────────────┬───────┬───��
�────┬───────────┬────────�
��─┐
│  username  │ admin │ common │ mailusers │ readonly │
├────────────┼───────┼───��
�────┼───────────┼────────�
��─┤
│ daniel     │     │        │ ✓        │      │
│ drupal     │     │        │       │           │
│ dv           │ ✓       │ ✓    │          │       │
│ extc       │     │        │       │ ✓     │
│ extu       │     │        │       │           │
│ foobar     │     │        │       │           │
│ joel       │     │        │       │           │
│ mailreader │     │        │       │ ✓     │
│ manitou    │ ✓       │          │ ✓          │       │
│ postgres   │     │        │       │           │
│ u1           │     │ ✓      │        │      │
│ u2           │     │        │ ✓        │      │
│ zaz        │     │        │ ✓        │      │
└────────────┴───────┴───��
�────┴───────────┴────────�
��─┘


What I like in that representation is that it looks good enough
to be pasted directly into a document in a word processor.

3. It can be rotated easily in the other direction, with:
   \rotate 2 1

(Cut horizontally to fit in a mail, the actual output is 116 chars wide).

                           Rotated query results
┌───────────┬────────┬───��
�────┬────┬──────┬──────┬─�
��──────┬──────┬────
│ username  │ daniel │ drupal │ dv │ extc │ extu │ foobar │
joel │ mai...
├───────────┼────────┼───��
�────┼────┼──────┼──────┼─�
��──────┼──────┼────
│ mailusers │ ✓       │          │    │      │      │      │
     │
│ admin     │     │        │ ✓  │      │      │      │
     │
│ common    │     │        │ ✓  │      │      │      │
     │
│ readonly  │     │        │    │ ✓    │      │      │
     │ ✓
└───────────┴────────┴───��
�────┴────┴──────┴──────┴─�
��──────┴──────┴────


4. Example with 3 columns and a count as the value to visualize along
two axis: date and category.
I'm using the number of mails posted per month in a few PG mailing lists,
broken down by list (which are tags in my schema).

Query:
 SELECT date_trunc('month', msg_date)::date as month,
   t.name,
   count(*) as cnt
 FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
 WHERE t.tag_id in (7,8,12,34,79)
 AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
 GROUP BY date_trunc('month', msg_date)::date, t.name
 ORDER BY 1,2;

Results:
   month    |     name      | cnt
------------+-------------+------
 2014-05-01 | announce      |   19
 2014-05-01 | general      |  550
 2014-05-01 | hackers      | 1914
 2014-05-01 | interfaces  |    4
 2014-05-01 | performance |  122
 2014-06-01 | announce      |   10
 2014-06-01 | general      |  499
 2014-06-01 | hackers      | 2008
 2014-06-01 | interfaces  |   10
 2014-06-01 | performance |  137
 2014-07-01 | announce      |   12
 2014-07-01 | general      |  703
 2014-07-01 | hackers      | 1504
 2014-07-01 | interfaces  |    6
 2014-07-01 | performance |  142
 2014-08-01 | announce      |    9
 2014-08-01 | general      |  616
 2014-08-01 | hackers      | 1864
 2014-08-01 | interfaces  |   11
 2014-08-01 | performance |  116
 2014-09-01 | announce      |   10
 2014-09-01 | general      |  645
 2014-09-01 | hackers      | 2364
 2014-09-01 | interfaces  |    3
 2014-09-01 | performance |  105
 2014-10-01 | announce      |   13
 2014-10-01 | general      |  476
 2014-10-01 | hackers      | 2325
 2014-10-01 | interfaces  |   10
 2014-10-01 | performance |  137
 2014-11-01 | announce      |   10
 2014-11-01 | general      |  457
 2014-11-01 | hackers      | 1810
 2014-11-01 | performance |  109
 2014-12-01 | announce      |   11
 2014-12-01 | general      |  623
 2014-12-01 | hackers      | 2043
 2014-12-01 | interfaces  |    1
 2014-12-01 | performance |   71
(39 rows)

\rotate gives:
            Rotated query results
   month    | announce | general | hackers | interfaces | performance
------------+----------+---------+---------+------------+-------------
 2014-05-01 | 19       | 550     | 1914    | 4        | 122
 2014-06-01 | 10       | 499     | 2008    | 10     | 137
 2014-07-01 | 12       | 703     | 1504    | 6        | 142
 2014-08-01 | 9        | 616     | 1864    | 11     | 116
 2014-09-01 | 10       | 645     | 2364    | 3        | 105
 2014-10-01 | 13       | 476     | 2325    | 10     | 137
 2014-11-01 | 10       | 457     | 1810    |        | 109
 2014-12-01 | 11       | 623     | 2043    | 1        | 71

Advantage: we can figure out the trends, and notice empty slots,
  much quicker than with the previous output. It seems smaller
  but there is the same amount of information.


5. Example with an additional column showing if the count grows up or down
   compared to the previous month. This shows how the contents get stacked
   inside cells when they come from several columns and rows.

Query:

SELECT to_char(mon, 'yyyy-mm') as month,
 name,
 CASE when lag(name,1) over(order by name,mon)=name then
   case sign(cnt-(lag(cnt,1) over(order by name,mon)))
    when 1 then chr(8593)
    when 0 then chr(8597)
    when -1 then chr(8595)
    else ' ' end
 END,
 cnt
 from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*) as
cnt
   FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
    WHERE t.tag_id in (7,8,12,34,79)
    AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
    GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

Result:
  month  |    name     | case | cnt
---------+-------------+------+------
 2014-05 | announce    |      |   19
 2014-06 | announce    | ↓    |   10
 2014-07 | announce    | ↑    |   12
 2014-08 | announce    | ↓    |    9
 2014-09 | announce    | ↑    |   10
 2014-10 | announce    | ↑    |   13
 2014-11 | announce    | ↓    |   10
 2014-12 | announce    | ↑    |   11
 2014-05 | general     |      |  550
 2014-06 | general     | ↓    |  499
 2014-07 | general     | ↑    |  703
 2014-08 | general     | ↓    |  616
 2014-09 | general     | ↑    |  645
 2014-10 | general     | ↓    |  476
 2014-11 | general     | ↓    |  457
 2014-12 | general     | ↑    |  623
 2014-05 | hackers     |      | 1914
 2014-06 | hackers     | ↑    | 2008
 2014-07 | hackers     | ↓    | 1504
 2014-08 | hackers     | ↑    | 1864
 2014-09 | hackers     | ↑    | 2364
 2014-10 | hackers     | ↓    | 2325
 2014-11 | hackers     | ↓    | 1810
 2014-12 | hackers     | ↑    | 2043
 2014-05 | interfaces  |      |    4
 2014-06 | interfaces  | ↑    |   10
 2014-07 | interfaces  | ↓    |    6
 2014-08 | interfaces  | ↑    |   11
 2014-09 | interfaces  | ↓    |    3
 2014-10 | interfaces  | ↑    |   10
 2014-12 | interfaces  | ↓    |    1
 2014-05 | performance |      |  122
 2014-06 | performance | ↑    |  137
 2014-07 | performance | ↑    |  142
 2014-08 | performance | ↓    |  116
 2014-09 | performance | ↓    |  105
 2014-10 | performance | ↑    |  137
 2014-11 | performance | ↓    |  109
 2014-12 | performance | ↓    |   71
(39 rows)

\rotate:

               Rotated query results
  month  | announce | general | hackers | interfaces | performance
---------+----------+---------+---------+------------+-------------
 2014-05 | 19        | 550     | 1914    | 4         | 122
 2014-06 | ↓ 10     | ↓ 499   | ↑ 2008  | ↑ 10         | ↑ 137
 2014-07 | ↑ 12     | ↑ 703   | ↓ 1504  | ↓ 6         | ↑ 142
 2014-08 | ↓ 9      | ↓ 616   | ↑ 1864  | ↑ 11         | ↓ 116
 2014-09 | ↑ 10     | ↑ 645   | ↑ 2364  | ↓ 3         | ↓ 105
 2014-10 | ↑ 13     | ↓ 476   | ↓ 2325  | ↑ 10         | ↑ 137
 2014-11 | ↓ 10     | ↓ 457   | ↓ 1810  |        | ↓ 109
 2014-12 | ↑ 11     | ↑ 623   | ↑ 2043  | ↓ 1         | ↓ 71
(8 rows)

The output columns 3 and 4 of the same row get projected into the same
cell, laid out horizontally (separated by space).

6. Example with the same query but rotated differently so that
  it's split into two columns: the counts that go up from the previous
  and those that go down. I'm also cheating a bit by
  casting name and cnt to char(N) for a better alignment

SELECT to_char(mon, 'yyyy-mm') as month,
 name::char(12),
 CASE when lag(name,1) over(order by name,mon)=name then
   case sign(cnt-(lag(cnt,1) over(order by name,mon)))
    when 1 then chr(8593)
    when 0 then chr(8597)
    when -1 then chr(8595)
    else ' ' end
 END,
 cnt::char(8)
 from (SELECT date_trunc('month', msg_date)::date as mon, t.name,count(*) as
cnt
   FROM mail JOIN mail_tags using(mail_id) JOIN tags t
on(t.tag_id=mail_tags.tag)
    WHERE t.tag_id in (7,8,12,34,79)
    AND msg_date>='2014-05-01'::date and msg_date<'2015-01-01'::date
    GROUP BY date_trunc('month', msg_date)::date, t.name) l order by 2,1;

 \rotate 1 3

+---------+-----------------------+-----------------------+
|  month  |          ↑        |        ↓          |
+---------+-----------------------+-----------------------+
| 2014-05 |              |              |
| 2014-06 | hackers     2008     +| announce     10     +|
|      | interfaces     10     +| general     499      |
|      | performance  137      |              |
| 2014-07 | announce     12     +| hackers     1504     +|
|      | general     703     +| interfaces     6      |
|      | performance  142      |              |
| 2014-08 | hackers     1864     +| announce     9     +|
|      | interfaces     11      | general     616     +|
|      |              | performance  116      |
| 2014-09 | announce     10     +| interfaces     3     +|
|      | general     645     +| performance  105      |
|      | hackers     2364      |              |
| 2014-10 | announce     13     +| general     476     +|
|      | interfaces     10     +| hackers     2325      |
|      | performance  137      |              |
| 2014-11 |              | announce     10     +|
|      |              | general     457     +|
|      |              | hackers     1810     +|
|      |              | performance  109      |
| 2014-12 | announce     11     +| interfaces     1     +|
|      | general     623     +| performance  71      |
|      | hackers     2043      |              |
+---------+-----------------------+-----------------------+

As there are several rows that match the vertical/horizontal filter,
(for example 3 results for 2014-06 as row and "arrow up" as column),
they are stacked vertically inside the cell, in addition to
"name" and "cnt" being shown side by side horizontally.

Note that no number show up for 2014-05; this is because they're not
associated with arrow up or down; empty as a column is discarded.
Maybe it shouldn't. In this case, the numbers for 2014-05 would be in a
column with an empty name.


Conclusion, the point of \rotate:

When analyzing query results, these rotated representations may be
useful or not depending on the cases, but the point is that they require
no effort to be obtained through \rotate X Y
It's so easy to play with various combinations to see if the result
makes sense, and if it reveals something about the data.
(it still reexecutes the query each time, tough).

We can get more or less the same results with crosstab/pivot, as it's the
same basic concept, but with much more effort spent on getting the SQL right,
plus the fact that columns not known in advance cannot be returned pivoted
in a single pass in SQL, a severe complication that the client-side doesn't
have.

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

On Sat, Aug 29, 2015 at 12:48:23AM +0200, Daniel Verite wrote:
>  Hi,
> 
> This is a reboot of my previous proposal for pivoting results in psql,
> with a new patch that generalizes the idea further through a command
> now named \rotate, and some examples.

Neat!

Thanks for putting this together :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  I wrote:

> What I like in that representation is that it looks good enough
> to be pasted directly into a document in a word processor.

And ironically, the nice unicode borders came out all garbled
in the mail, thanks to a glitch in my setup that mis-reformatted them
before sending.

Sorry about that, the results with unicode linestyle were supposed to be
as follows:

Example 2:
    Rotated query results
┌────────────┬───────┬────────┬───────────┬──────────┐
│  username  │ admin │ common │ mailusers │ readonly │
├────────────┼───────┼────────┼───────────┼──────────┤
│ daniel     │         │          │ ✓      │         │
│ drupal     │         │          │       │         │
│ dv         │ ✓     │ ✓      │       │         │
│ extc         │         │          │       │ ✓         │
│ extu         │         │          │       │         │
│ foobar     │         │          │       │         │
│ joel         │         │          │       │         │
│ mailreader │         │          │       │ ✓         │
│ manitou    │ ✓     │          │ ✓      │         │
│ postgres   │         │          │       │         │
│ u1         │         │ ✓      │       │         │
│ u2         │         │          │ ✓      │         │
│ zaz         │         │          │ ✓      │         │
└────────────┴───────┴────────┴───────────┴──────────┘


Example 3, rotated in the other direction

(Cut horizontally to fit in a mail, the actual output is 116 chars wide).
                       Rotated query results
┌───────────┬────────┬────────┬────┬──────┬──────┬────────┬──────┬────
│ username  │ daniel │ drupal │ dv │ extc │ extu │ foobar │ joel │ mai...
├───────────┼────────┼────────┼────┼──────┼──────┼────────┼──────┼────
│ mailusers │ ✓      │          │    │      │     │      │     │
│ admin     │         │          │ ✓  │      │     │      │     │
│ common    │         │          │ ✓  │      │     │      │     │
│ readonly  │         │          │    │ ✓      │     │      │     │ ✓
└───────────┴────────┴────────┴────┴──────┴──────┴────────┴──────┴────

    Pavel Stehule wrote:

> the name "rotate" is not correct - maybe "\cross" ?

I'm not dead set on \rotate and suggested other names
previously in [1], but none of them seems decisively
superior.

The rationale behind rotate is that, it's a synonym of pivot
as a verb,  and it's not already used for other things in SQL.

Incidentally I'm discovering by googling that people actually
searched previously for that feature with that name:
http://postgresql.nabble.com/rotate-psql-output-td3046832.html

OTOH "cross" is already used in the database vocabulary for
cross joins. Also I find it used too in "cross-db queries" or the
"cross apply" of other engines.
I think  that plays against it for choosing it to designate
something different again.

However, maybe \across may be a better fit, or "cross"
combined with some other word, as in \crossview .
Not sure how that sounds to a native english speaker.


[1]
http://www.postgresql.org/message-id/cd521513-1349-4698-b93c-693199962e23@mm

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

On Fri, Sep 4, 2015 at 5:08 PM, Daniel Verite <daniel@manitou-mail.org> wrote:
> I'm not dead set on \rotate and suggested other names
> previously in [1], but none of them seems decisively
> superior.


Fwiw I like \rotate. It's pretty clear what it means and it sounds
similar to but not exactly the same as pivot.

-- 
greg

On Mon, Sep 7, 2015 at 5:08 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Given the role that psql performs I do think \rotate to be the least
> problematic choice; I concur that avoiding \pivot is desirable due to SQL's
> usage.

I can't agree.  Rotating a matrix has a well-defined meaning, and this
does something that is not that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Sep 8, 2015 at 2:10 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Sep 8, 2015 at 1:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Sep 7, 2015 at 5:08 PM, David G. Johnston
>> <david.g.johnston@gmail.com> wrote:
>> > Given the role that psql performs I do think \rotate to be the least
>> > problematic choice; I concur that avoiding \pivot is desirable due to
>> > SQL's
>> > usage.
>>
>> I can't agree.  Rotating a matrix has a well-defined meaning, and this
>> does something that is not that.
>
> Even though the input data is a table and not a matrix?

Yes, I think rotating a table also has a pretty well-defined meaning.

> Do you have an
> alternative choice you'd like to defend?

Not particularly.  If everybody picks one thing they like and argues
strenuously for it, we'll never get anywhere.  I think it's enough to
say that I think this particular choice isn't the best.  It's not as
if no other suggestions have been made.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

    Pavel Stehule wrote:

> rotate ~ sounds like transpose matrix, what is not true in this case.

The various definitions that I can see, such as
http://dictionary.reference.com/browse/rotate
make no mention of matrices. It applies to anything that
moves around a pivot or axis.

OTOH, the established term for the matrix operation you're
referring to appears to be "transpose", as you mention.
https://en.wikipedia.org/wiki/Transpose

I notice that according to
http://www.thesaurus.com/browse/transpose
"rotate" is not present in the 25+ synonyms they suggest for
"transpose".

In the above wikipedia article about matrix transposition,
"rotate" is also never used anywhere.

"rotate matrix" does not exist for google ngrams, whereas
"transpose matrix" does.
https://books.google.com/ngrams

Overall I don't see the evidence that "rotate" alone  would
suggest transposing a matrix.

Now it appears that there is a concept in linear algebra named
"rotation matrix", defined as:
https://en.wikipedia.org/wiki/Rotation_matrix
that seems quite relevant for 3D software.

But as psql is not a tool for linear algebra or 3D in the first place,
who could realistically be deceived?


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

On Tue, Sep 8, 2015 at 4:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2015-09-08 22:55 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:
>>
>>         Pavel Stehule wrote:
>>
>> > rotate ~ sounds like transpose matrix, what is not true in this case.
>
> for me the relation rotation is exactly what \x does

\x doesn't exactly rotate it either.  \x puts the column headers down
the side instead of across the top, but it doesn't put the rows across
the top instead of down the side.  Rather, each row is listed in a
separate chunk.  This feature is doing something else again.  I've
actually never seen this particular transformation anywhere except for
Microsoft Excel's pivot tables, which I still find confusing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  Hi,

This is the 2nd iteration of this patch, for comments and review.

Changes:

- the arguments can be column names (rather than only numbers).

- the horizontal headers are sorted server-side according to their original
  type. DESC order is possible by prefixing the column arg with a minus sign.

- the command is now modelled after \g so it can be used
  in place of  \g

- the title is no longer set by the command, it was getting in the
  way when outputting to data file.

- there's a hard limit on 1600 columns. This is to fail early and clean
  on large resultsets that are not amenable to being rotated.

- includes SGML user doc.

As I don't have plans for further improvements, I'll submit this one
to the open commitfest.

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

<div dir="ltr"><br /><div class="gmail_extra">Hi<br /></div><div class="gmail_extra"><br /><div
class="gmail_quote">2015-09-1611:35 GMT+02:00 Daniel Verite <span dir="ltr"><<a
href="mailto:daniel@manitou-mail.org"target="_blank">daniel@manitou-mail.org</a>></span>:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">  Hi,<br /><br /> This is the
2nditeration of this patch, for comments and review.<br /><br /><br /><div class="HOEnZb"><div class="h5"><br
/></div></div></blockquote></div><br/></div><div class="gmail_extra">my comments:<br /><br /></div><div
class="gmail_extra">1.I don't understand why you are use two methods for sorting columns (qsort, and query with ORDER
BY)<br/><br /></div><div class="gmail_extra">2. Data column are not well aligned - numbers are aligned as text<br /><br
/></div><divclass="gmail_extra">3. When data are multiattribute - then merging together with space separator is not
practical<br/><br /></div><div class="gmail_extra">  * important information is lost<br /></div><div
class="gmail_extra"> * same transformation can be done as expression, so this feature is useless<br /><br /></div><div
class="gmail_extra">Ispossible to use one cell per attribute (don't do merge)?<br /><br /></div><div
class="gmail_extra">DATAQUERY: SELECT dim1, dim2, sum(x), avg(x) FROM .. GROUP BY dim1, dim2<br /><br /></div><div
class="gmail_extra">andresult header of rotate can be<br /><br /></div><div class="gmail_extra">DIM1   | dim2_val1/sum
|dim2_val1/avg | dim2_val2/sum | dim2_val2/avg | ...<br /><br /></div><div class="gmail_extra"><br /></div><div
class="gmail_extra"><br/></div></div> 

    Pavel Stehule wrote:

> in the help inside your last patch, you are using "crosstab". Cannto be
> crosstab the name for this feature?

If it wasn't taken already by contrib/tablefunc, that would be a first
choice. But now, when searching for crosstab+postgresql, pages of
results come out concerning the crosstab() function.

So not using \crosstab is deliberate; it's to prevent confusion with
the server-side function.


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

    Pavel Stehule wrote:


> my comments:
>
> 1. I don't understand why you are use two methods for sorting columns
> (qsort, and query with ORDER BY)

qsort (with strcmp as the comparator) is only used to determine the
set of distinct values for the vertical and horizontal headers.
In fact this is just to allow the use of bsearch() when doing that
instead of a slower sequential search.

Once the values for the horizontal headers are known, they
are passed to the server for sorting with server-side semantics
according to their type. The order will differ from qsort/strcmp
found as the comparison semantics are different.

The values for the vertical header are not sorted server-side
because we keep the order of the query for displaying
top to bottom.
In the typical use case , it will have ORDER BY 1[,2] possibly
with one/two DESC qualifiers.

> 2. Data column are not well aligned - numbers are aligned as text

Yes. I'll look shortly into fixing that.


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

    Pavel Stehule wrote:

> 2. Data column are not well aligned - numbers are aligned as text

Thanks for spotting that, it's fixed in the attached new iteration of
the patch.

> 3. When data are multiattribute - then merging together with space separator
> is not practical
>
>   * important information is lost
>   * same transformation can be done as expression, so this feature is
> useless

The primary use case is for queries with 3 output columns
(A,B,C) where A and B are dimensions and C is uniquely
determined by (A,B).

How columns 4 and above get displayed is not essential to the
feature, as it's somehow a degenerate case. As you note, it
could be avoided by the user limiting the query to 3 columns,
and providing whatever expression fits for the 3rd column.

Still if the query has > 3 columns, it has to be dealt with.
The choices I've considered:

a- Just error out.

b- Force the user to specify which single column should be taken
   as the value. That would be an additional argument to the command,
   or the fixed 3rd column in the invocation without arg.

c- Stack the values horizontally in the same cell with a separator.
   As the query implies f(A,B)=(C,D,E) we display C D E in the cell
   at coordinates (A,B). It's what it does currently.

[a] is not very user friendly.
[b] seems acceptable. It discards columns but the user decides which.
[c] is meant as a best effort at not discarding anything.

When [c] gives poor results, the next step from my point of view
would be for the user to rework the query, just like in the general case
when a query is not satisfying.

You're suggesting a [d] choice, subdividing the horizontal headers.
It seems to me like a pretty radical change, multiplying the number
of columns, and it has also the potential to give poor results visually.
Let's see if more feedback comes.

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

    Pavel Stehule wrote:

> > So not using \crosstab is deliberate; it's to prevent confusion with
> > the server-side function.
>
> I don't afraid about this - crosstab is a function in extension. Psql
> backslash commands living in different worlds.

Sure, but the confusion would be assuming that \crosstab is some sort
of frontend for crosstab() queries,  like for example \copy is a frontend
for COPY.
That mistake seems plausible if the same name is reused, and much less
plausible otherwise.


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

        Pavel Stehule wrote:

> in the help inside your last patch, you are using "crosstab". Cannto be
> crosstab the name for this feature?

If it wasn't taken already by contrib/tablefunc, that would be a first
choice. But now, when searching for crosstab+postgresql, pages of
results come out concerning the crosstab() function.

<div dir="ltr">Hi<br /><div class="gmail_extra"><br /><div class="gmail_quote">I am looking on this last patch. I
talkedabout the name of this command with more people, and the name "rotate" is unhappy. The correct name for this
visualizationtechnique is "crosstab" (see google "crosstab"). The conflict with our extension is unhappy, but using
"rotate"is more worst - (see google "rotate"). The term "rotate" is used less time (related to topic), and usually with
zeroinformed people. More, in attached doc, the word "crosstab" is pretty often used, and then the word "rotate" has
notsense.<br /><br /></div><div class="gmail_quote">The important question is sorting output. The vertical header is
sortedby first appearance in result. The horizontal header is sorted in ascending or descending order. This is
unfriendlyfor often use case - month names. This can be solved by third parameter - sort function. <br /> <br
/></div><divclass="gmail_quote">Regards<br /><br /></div><div class="gmail_quote">Pavel<br /></div></div></div> 

On 11/04/2015 04:09 AM, Pavel Stehule wrote:
> I am looking on this last patch. I talked about the name of this command
> with more people, and the name "rotate" is unhappy. The correct name for
> this visualization technique is "crosstab" (see google "crosstab"). The
> conflict with our extension is unhappy, but using "rotate" is more worst
> - (see google "rotate"). The term "rotate" is used less time (related to
> topic), and usually with zero informed people. More, in attached doc,
> the word "crosstab" is pretty often used, and then the word "rotate" has
> not sense.

Apologies if this has already been suggested (as I have not followed the
entire thread), but if you don't want to conflict with the name
crosstab, perhaps "pivot" would be better?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

    Pavel Stehule wrote:

> I am looking on this last patch. I talked about the name of this command
> with more people, and the name "rotate" is unhappy. The correct name for
> this visualization technique is "crosstab" (see google "crosstab"). The
> conflict with our extension is unhappy, but using "rotate" is more worst -
> (see google "rotate"). The term "rotate" is used less time (related to
> topic), and usually with zero informed people. More, in attached doc, the
> word "crosstab" is pretty often used, and then the word "rotate" has not
> sense.

First, thanks for looking again at the patch and for your feedback.

I note that you dislike and oppose the current name, as previously
when that choice of name was discussed quite a bit.
However I disagree that "rotate" doesn't make sense. On the semantics
side, several people have expressed upthread that it was OK, as a
plausible synonym for  "pivot". If it's unencumbered by previous use
in this context, then all the better, I'd say why not corner it for our
own use?
It's not as if we had to cling to others people choices for psql
meta-commands.

Anyway that's just a name. It shall be changed eventually to
whatever the consensus is, if one happens to emerge.

> The important question is sorting output. The vertical header is
> sorted by first appearance in result. The horizontal header is
> sorted in ascending or descending order. This is unfriendly for
> often use case - month names. This can be solved by third parameter
> - sort function.

Right, it's not possible currently to sort the horizontal header by
something else than the values in it.
I agree that it would be best to allow it if there's a reasonable way to
implement it. I'm not sure about letting the user provide a function
in argument.
In the case of the month names example, the function
f(monthname)=number-of-month may not exist. If the
user has to create it beforehand, it feels a bit demanding
for a display feature.

I wonder if this ordering information could be instead deduced
somehow from the non-pivoted resultset at a lower cost.
I'll try to think more and experiment around this.


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

    Joe Conway wrote:

> but if you don't want to conflict with the name
> crosstab, perhaps "pivot" would be better?

Initially I had chosen \pivot without much thought about it,
but the objection was raised that a PIVOT/UNPIVOT SQL feature
would likely exist in core in a next release independantly from psql.

If things unfold as envisioned, we would end up in the future with:
- crosstab() from tablefunc's contrib module.
- SELECT (...) PIVOT [serialization?] (...) in the SQL grammar.
- \rotate or yet another name for the client-side approach.

The reason to avoid both \crosstab or \pivot for the psql feature is
the fear of confusion for the less expert users who don't feel
the clear-cut separation between core and extensions and client
versus server, they mostly know that they're trying to use
a feature named $X. When they search for $X, it's better when
they don't find something else and possibly jump to wrong
conclusions about what it does and how it works.

Or maybe that's worrying about things that will never matter in
reality, that's possible too.


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

On Wed, Nov 04, 2015 at 08:20:28AM -0800, Joe Conway wrote:
> On 11/04/2015 04:09 AM, Pavel Stehule wrote:
> > I am looking on this last patch. I talked about the name of this command
> > with more people, and the name "rotate" is unhappy. The correct name for
> > this visualization technique is "crosstab" (see google "crosstab"). The
> > conflict with our extension is unhappy, but using "rotate" is more worst
> > - (see google "rotate"). The term "rotate" is used less time (related to
> > topic), and usually with zero informed people. More, in attached doc,
> > the word "crosstab" is pretty often used, and then the word "rotate" has
> > not sense.
> 
> Apologies if this has already been suggested (as I have not followed the
> entire thread), but if you don't want to conflict with the name
> crosstab, perhaps "pivot" would be better?

As I mentioned earlier, I'm hoping we can keep PIVOT reserved for the
server side, where all our competitors except DB2 (and MySQL if you
count that) have it.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 5 November 2015 at 05:22, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> If I understand to text on wiki, the name is used for tool, that can do
> little bit more things, but it is often used for this technique (so it is
> much better than "rotate"). I don't understand well, why "crosstab" is too
> wrong name. This is pretty similar to COPY - and I know, so in first minutes
> hard to explain this difference between COPY and \copy to beginners, but
> after day of using there is not any problem.

I see constant confusion between \copy and COPY. It's a really good
reason NOT to overload other psql commands IMO.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 11/04/2015 10:46 PM, Pavel Stehule wrote:
> 2015-11-05 7:39 GMT+01:00 Craig Ringer wrote:
>     I see constant confusion between \copy and COPY. It's a really good
>     reason NOT to overload other psql commands IMO.
>
> but crosstab is one old function from old extension with unfriendly
> design.

Hey, I resemble that remark ;-)

> When we support PIVOT/UNPIVOT - the crosstab function will be
> obsolete. It is not often used command.

But agreed, once we have proper support for PIVOT built into the
grammar, the entire tablefunc extension becomes obsolete, so perhaps
overloading \crosstab is not so bad.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway wrote:
> On 11/04/2015 10:46 PM, Pavel Stehule wrote:

> > but crosstab is one old function from old extension with unfriendly
> > design.
> 
> Hey, I resemble that remark ;-)

You may be old all you want, but certainly not unfriendly!

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 11/05/2015 12:56 PM, Pavel Stehule wrote:
> 2015-11-05 17:17 GMT+01:00 Joe Conway wrote:
>     Hey, I resemble that remark ;-)
>
> I am sorry, Joe - no any personal attack - I'll pay a beer for you if
> you visit Prague :)

No offense taken, but I might take you up on that beer someday ;-)

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

    Pavel Stehule wrote:

> [ \rotate being a wrong name ]

Here's an updated patch.

First it renames the command to \crosstabview, which hopefully may
be more consensual, at least it's semantically much closer to crosstab .

> The important question is sorting output. The vertical header is
> sorted by first appearance in result. The horizontal header is
> sorted in ascending or descending order. This is unfriendly for
> often use case - month names. This can be solved by third parameter
> - sort function.

I've thought that sorting with an external function would be too
complicated for this command, but sorting ascending by default
was not the right choice either.
So I've changed to sorting by first appearance in result (like the vertical
header), and sorting ascending or descending only when specified
(with +colH or -colH syntax).

So the synopsis becomes: \crosstabview [ colV [+ | -]colH ]

Example with a time series (daily mean temperatures in Paris,2014),
month names across, day numbers down :

select
  to_char(w_date,'DD') as day ,
  to_char(w_date,'Mon') as month,
  w_temp from weather
  where w_date between '2014-01-01' and '2014-12-31'
  order by w_date
\crosstabview

 day | Jan | Feb | Mar | Apr | May | Jun | ...[cut]
-----+-----+-----+-----+-----+-----+-----+-
 01  |     8 |   8 |   6 |  16 |    12 |  15 |
 02  |    10 |   6 |   6 |  15 |    12 |  16 |
 03  |    11 |   5 |   7 |  14 |    11 |  17 |
 04  |    10 |   6 |   8 |  12 |    12 |  14 |
 05  |     6 |   7 |   8 |  14 |    16 |  14 |
 06  |    10 |   9 |   9 |  16 |    17 |  20 |
 07  |    11 |  10 |  10 |  18 |    14 |  24 |
 08  |    11 |   8 |  12 |  10 |    13 |  22 |
 09  |    10 |   6 |  14 |  12 |    16 |  22 |
 10  |     6 |   7 |  14 |  14 |    14 |  19 |
 11  |     7 |   6 |  12 |  14 |    12 |  21 |
...cut..
 28  |     4 |   7 |  10 |  12 |    14 |  16 |
 29  |     4 |     |  14 |  10 |    15 |  16 |
 30  |     5 |     |  14 |  14 |    17 |  18 |
 31  |     5 |     |  14 |     |    16 |     |

The month names come out in the expected order here,
contrary to what happened with the previous iteration of
the patch which forced a sort in all cases.
Here it plays out well because the single "ORDER BY w_date" is
simultaneously OK for the vertical and horizontal headers,
a common case for time series.

For more complicated cases, when the horizontal and vertical
headers should be ordered independantly, and
in addition the horizontal header should not be sorted
by its values, I've toyed with the idea of sorting by another
column which would supposedly be added in the query
just for sorting, but it loses much in simplicity. For the more
complex stuff, users can always turn to the server-side methods
if needed.

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

    Pavel Stehule wrote:

> postgres=# \crosstabview 4 +month label
>
> Maybe using optional int order column instead label is better - then you can
> do sort on client side
>
> so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol]

In the meantime I've followed a different idea: allowing the
vertical header to be sorted too, still server-side.

That's because to me, the first impulse for a user noticing that
it's not sorted vertically would be to write
 \crosstabview +customer month
rather than figure out the
 \crosstabview customer +month_number month_name
invocation.
But both ways aren't even mutually exclusive. We could support
 \crosstabview [+|-]colV[:labelV] [+|-]colH[:labelH]
it's more complicated to understand, but not  harder to implement.

Also, a non-zero FETCH_COUNT is supported by this version of the patch,
if the first internal FETCH retrieves less than FETCH_COUNT rows.
Otherwise a specific error is emitted.

Also there are minor changes in arguments and callers following
recent code changes for \o

Trying to crosstab with 10k+ distinct values vertically, I've noticed
that the current code is too slow, spending too much time
sorting.  I'm currently replacing its simple arrays of distinct values
with AVL binary trees, which I expect to be much more efficient for
this.

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

    Pavel Stehule wrote:


> here is patch - supported syntax: \crosstabview VCol [+/-]HCol [HOrderCol]
>
> Order column should to contains any numeric value. Values are sorted on
> client side

If I understand correctly, I see a problem with HOrderCol.

If the vertical header consists of, for example, a series of
event names, and it should be sorted  by event date, then
the requirement of HOrderCol being strictly numeric is
problematic,  in a way that the previous proposal was not, isn't it?

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

    Pavel Stehule wrote:

> The symbol 'X' in two column mode should be centred - now it is aligned to
> left, what is not nice

Currently print.c does not support centered alignment, only left and right.
Should we add it, it would have to work for all output formats
(except obviously for "unaligned"):
- aligned
- wrapped
- html
- latex
- latex-longtable
- troff-ms
- asciidoc

Because of this, I believe that adding support for a 'c' alignment
might be a significant patch by itself, and that it should be considered
separately.

I agree that if it existed, the crosstabview command should use it
as you mention, but I'm not volunteering to implement it myself, at
least not in the short term.

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

On Sun, Dec 20, 2015 at 6:49 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> attached patch allows align to center.
>
> everywhere where left/right align was allowed, the center align is allowed

Moved to next CF, there is a fresh and new patch.
-- 
Michael

   Hi,

Here's an updated patch that replaces sorted arrays by AVL binary trees
when gathering distinct values for the columns involved in the pivot.
The change is essential for large resultsets. For instance,
it allows to process a query like this (10 million rows x 10 columns):

select x,(random()*10)::int, (random()*1000)::int from
  generate_series(1,10000000) as x
 \crosstabview

which takes about 30 seconds to run and display on my machine with the
attached patch. That puts it seemingly in the same ballpark than
the equivalent test with the server-side crosstab().

With the previous iterations of the patch, this test would never end,
even with much smaller sets, as the execution time of the 1st step
grew exponentially with the number of distinct keys.
The exponential effect starts to be felt at about 10k values on my low-end
CPU,
and from there quickly becomes problematic.

As a client-side display feature, processing millions of rows like in
the query above does not necessarily make sense, it's pushing the
envelope, but stalling way below 100k rows felt lame, so I'm happy to get
rid of that limitation.

However, there is another one. The above example does not need or request
an additional sort step, but if it did, sorting more than 65535 entries in
the vertical header would error out, because values are shipped as
parameters to PQexecParams(), which only accepts that much.
To avoid the problem, when the rows in the output "grid" exceed 2^16 and
they need to be sorted, the user must  let the sort being driven by ORDER BY
beforehand in the query, knowing that the pivot will keep the original
ordering intact in the vertical header.

I'm still thinking about extending this based on Pavel's diff for the
"label" column, so that
 \crosstabview [+|-]colV[:colSortH] [+|-]colH[:colSortH]
would mean to use colV/H as grid headers but sort them according
to colSortV/H.
I prefer that syntax over adding more parameters, and also I'd like
to have it work in both V and H directions.

Aside from the AVL trees, there are a few other minor changes in that
patch:
- move non-exportable structs from the .h to the .c
- move code in common.c to respect alphabetical ordering
- if vertical sort is requested, add explicit check against more than 65535
  params instead of letting the sort query fail
- report all failure cases of the sort query
- rename sortColumns to serverSort and use less the term "columns" in
  comments and variables.


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

  Hi,

Here's an updated patch improving on how the horizontal and vertical
headers can be sorted.

The discussion upthread went into how it was desirable
to have independant sorts for these headers, possibly driven
by another column, in addition to the query's ORDER BY.

Thus the options now accepted are:

\crosstabview [ [-|+]colV[:scolV] [-|+]colH[:scolH]  [colG1[,colG2...]] ]

The optional scolV/scolH columns drive sorts for respectively
colV/colH (colV:scolV somehow means SELECT colV from... order by scolV)

colG1,... in 3rd arg indicate the columns whose contents form the grid
cells, the typical use case being that there's only one such column.
By default it's all columns minus colV and colH.

For example,

SELECT
  cust_id,
  cust_name,
  cust_date,
  date_part('month, sales_date),
  to_char(sales_date, 'Mon') as month,
  amount
FROM sales_view
WHERE [predicates]
[ORDER BY ...]

If we want to look at <amount> in a grid with months names across, sorted
by month number, and customer name in the vertical header, sorted by date of
acquisition, we could do this:

\crosstabview +cust_name:cust_date +5:4 amount

or letting the vertical header being sorted by the query's ORDER BY,
and the horizontal header same as above:

\crosstabview cust_name +5:4 amount

or sorting vertically by name, if it happens that the ORDER BY is missing or
is on something else:

\crosstabview +cust_name +5:4 amount


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

    Pavel Stehule wrote:

> 1. maybe we can decrease name to shorter "crossview" ?? I am happy with
> crosstabview too, just crossview is correct too, and shorter

I'm in favor of keeping crosstabview. It's more explicit, only
3 characters longer and we have tab completion anyway.

> 2. Columns used for ordering should not be displayed by default. I can live
> with current behave, but hiding ordering columns is much more practical for
> me

I can see why, but I'm concerned about a consequence:
say we have 4 columns A,B,C,D and user does \crosstabview +A:B +C:D
If B and D are excluded by default, then there's nothing
left to display inside the grid.
It doesn't feel quite right. There's something counter-intuitive
in the fact that values in the grid would disappear depending on
whether and how headers are sorted.
With the 3rd argument, we let the user decide what they want
to see.

> 3. This code is longer, so some regress tests are recommended - attached
> simple test case

I've added a few regression tests to the psql testsuite
based on your sample data. New patch with these tests
included is attached, make check passes.

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

<div dir="ltr"><div class="gmail_extra">Hi</div><div class="gmail_extra"><br /></div><div class="gmail_extra">I tested
lastversion, v11 and I have not any objection</div><div class="gmail_extra"><br /></div><div class="gmail_extra">It is
workingas expected</div><div class="gmail_extra"><br /></div><div class="gmail_extra">all regress tests passed, there
isrelated documentation and new test is attached.</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Thispatch is ready form commiter.</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Daniel,thank you very much, it is interesting feature.</div><div class="gmail_extra"><br
/></div><divclass="gmail_extra">Regards</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Pavel</div></div>

Hi!

Interesting feature, but it's not very obvious how to use it. I'd like to see 
some example(s) in documentation.

And I see an implementation of AVL tree in psql source code 
(src/bin/psql/crosstabview.c). Postgres already has a Red-Black tree 
implementation in
src/include/lib/rbtree.h and src/backend/lib/rbtree.c. Is any advantage of using 
AVL tree here? I have some doubt about that and this implementation, obviously, 
will not be well tested. But I see in comments that implementation is reduced to 
insert only and it doesn't use the fact of ordering tree, so, even hash could be 
used.

Daniel Verite wrote:
>     Pavel Stehule wrote:
>
>> 1. maybe we can decrease name to shorter "crossview" ?? I am happy with
>> crosstabview too, just crossview is correct too, and shorter
>
> I'm in favor of keeping crosstabview. It's more explicit, only
> 3 characters longer and we have tab completion anyway.
>
>> 2. Columns used for ordering should not be displayed by default. I can live
>> with current behave, but hiding ordering columns is much more practical for
>> me
>
> I can see why, but I'm concerned about a consequence:
> say we have 4 columns A,B,C,D and user does \crosstabview +A:B +C:D
> If B and D are excluded by default, then there's nothing
> left to display inside the grid.
> It doesn't feel quite right. There's something counter-intuitive
> in the fact that values in the grid would disappear depending on
> whether and how headers are sorted.
> With the 3rd argument, we let the user decide what they want
> to see.
>
>> 3. This code is longer, so some regress tests are recommended - attached
>> simple test case
>
> I've added a few regression tests to the psql testsuite
> based on your sample data. New patch with these tests
> included is attached, make check passes.
>
> Best regards,
>
>
>
>

-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 

    Teodor Sigaev wrote:

> Interesting feature, but it's not very obvious how to use it. I'd like to
> see  some example(s) in documentation.

I'm thinking of making a wiki page, because examples pretty much
require showing resultsets, and I'm not sure this would fly
with our current psql documentation, which is quite compact.

The current bit of doc I've produced is 53 lines long in manpage
format already. The text has not been proofread by a native
English speaker yet, so part of the problem might be that
it's just me struggling with english :)

> And I see an implementation of AVL tree in psql source code
> (src/bin/psql/crosstabview.c). Postgres already has a Red-Black tree
> implementation in src/include/lib/rbtree.h and
> src/backend/lib/rbtree.c. Is any advantage of using AVL tree here? I
> have some doubt about that and this implementation, obviously, will
> not be well tested. But I see in comments that implementation is
> reduced to insert only and it doesn't use the fact of ordering tree,
> so, even hash could be used.

Yes. I expect too that a RB tree or a hash-based algorithm would do
the job and perform well.

The AVL implementation in crosstabview is purposely simplified
and specialized for this job, resulting in ~185 lines of code
versus ~850 lines for rb-tree.c
But I understand the argument that the existing rb-tree has been
battle-tested, whereas this code hasn't.

I'm looking at rb-tree.c and thinking what it would take to
incorporate it:
1. duplicating or linking from backend/lib/rb-tree.c into psql/
2. replacing the elog() calls with something else in the case of psql
3. updating the crosstabview data structures and call sites.

While I'm OK with #3, #1 and #2 seem wrong.
I could adapt rb-tree.c so that the same code can be used
both client-side and server-side, but touching server-side
code for this feature and creating links in the source tree
feels invasive and overkill.

Another approach is to replace AVL with an hash-based algorithm,
but that raises the same sort of question. If crosstabview comes
with its specific implementation, why use that rather than existing
server-side code? But at a glance, utils/hash/dynahash.c seems quite
hard to convert for client-side use.

I'm open to ideas on this. In particular, if we have a hash table
implementation that is already blessed by the project and small enough
to make sense in psql, I'd be happy to consider it.

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

Daniel Verite wrote:
>     Teodor Sigaev wrote:
> 
> > Interesting feature, but it's not very obvious how to use it. I'd like to
> > see  some example(s) in documentation.
> 
> I'm thinking of making a wiki page, because examples pretty much
> require showing resultsets, and I'm not sure this would fly
> with our current psql documentation, which is quite compact.

Yeah, we need to keep in mind that the psql doc is processed as a
manpage also, so it may not be a great idea to add too many things
there.  But I also agree that some good examples would be useful.

FWIW I think the general idea of this feature (client-side resultset
"pivoting") is a good one, but I don't really have an opinion regarding
your specific proposal.  I think you should first seek some more
consensus about the proposed design; in your original thread [1] several
guys defended the idea of having this be a psql feature, and the idea of
this being a parallel to \x seems a very sensible one, but there's
really been no discussion on whether your proposed "+/-" syntax to
change sort order makes sense, for one thing.

So please can we have that wiki page so that the syntax can be hammered
out a bit more.

I'm closing this as returned-with-feedback for now.

[1] It's a good idea to add links to previous threads where things were
discussed.  I had to search for
www.postgresql.org/message-id/flat/78543039-c708-4f5d-a66f-0c0fbcda1f76@mm
because you didn't provide a link to it when you started the second
thread.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Pavel Stehule wrote:

> > FWIW I think the general idea of this feature (client-side resultset
> > "pivoting") is a good one, but I don't really have an opinion regarding
> > your specific proposal.  I think you should first seek some more
> > consensus about the proposed design; in your original thread [1] several
> > guys defended the idea of having this be a psql feature, and the idea of
> > this being a parallel to \x seems a very sensible one,

Sorry, I meant \q here, not \x.

> > but there's really been no discussion on whether your proposed "+/-"
> > syntax to change sort order makes sense, for one thing.
> 
> I am sorry, but I disagree - the discussion about implementation was more
> than two months, and I believe so anybody who would to discuss had enough
> time to discuss. This feature and design was changed significantly and
> there was not anybody who sent feature design objection.

I just rechecked the thread.  In my reading, lots of people argued
whether it should be called \rotate or \pivot or \crosstab; it seems the
\crosstabview proposal was determined to be best.  I can support that
decision.  But once the details were discussed, it was only you and
Daniel left in the thread; nobody else participated.  While I understand
that you may think that "silence is consent", what I am afraid of is
that some committer will look at this two months from now and say "I
hate this Hcol+ stuff, -1 from me" and send the patch back for syntax
rework.  IMO it's better to have more people chime in here so that the
patch that we discuss during the next commitfest is really the best one
we can think of.

Also, what about the business of putting "x" if there's no third column?
Three months from now some Czech psql hacker will say "we should use
Unicode chars for this" and we will be forever stuck with \pset
unicode_crosstab_marker to change the character to a ☑ BALLOT BOX WITH
CZECH.  Maybe we should think that a bit harder -- for example, what
about just rejecting the case with no third column and forcing the user
to add a third column with the character they choose?  That way you
avoid that mess.

> This feature has only small relation to SQL PIVOTING feature - it is just
> form of view and I am agree with Daniel about sense of this feature.

Yes, I don't disagree there.  Robert Haas and David Fetter also
expressed their support for psql-side processing, so I think we're good
there.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 9 February 2016 at 05:24, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> I have not a feeling so we did some with Daniel privately. All work was
> public (I checked my mailbox) - but what is unhappy - in more mailing list
> threads (not sure how it is possible, because subjects looks same). The
> discus about the design was public, I am sure. It was relative longer
> process, with good progress (from my perspective), because Daniel accepts
> and fixed all my objection. The proposed syntax is fully consistent with
> other psql commands - hard to create something new there, because psql
> parser is pretty limited. Although I am thinking so syntax is good, clean
> and useful I am open to discuss about it. Please, try the last design, last
> patch - I spent lot of hours (and I am sure so Daniel much more) in thinking
> how this can be designed better.
>

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.

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

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

On 9 February 2016 at 10:09, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 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??
>

So it would just be

SELECT name,      to_char(date, 'mon') AS month,      sum(amount) AS amount,      extract(month from date) AS
month_orderFROMinvoicesGROUP BY 1,2,3ORDER BY name
 
\crosstabview name month amount month_order

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
invoicesGROUPBY 1,2,3ORDER BY name NULLS LAST
 
\crosstabview name month amount month_order asc nulls last

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.


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

Regards,
Dean

On 9 February 2016 at 11:06, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>   + respect SQL clauses ordering, allows pretty complex ORDER BY clause

That, to me is the key point. SQL already allows very powerful
sorting, so psql should not just throw away the query's sort order and
replace it with something much more basic and limited. The exact
syntax can be debated, but I don't think psql should be doing row
sorting.

I also don't believe that extending the +/- sort syntax to support
more advanced options will be particularly easy, and the result is
likely to be even less readable. It also requires the user to learn
another syntax, when they will already be familiar with SQL's sort
syntax.

Regards,
Dean

    Alvaro Herrera wrote:

> So please can we have that wiki page so that the syntax can be hammered
> out a bit more.

Sure, I'm on it.

> I'm closing this as returned-with-feedback for now.

Well,  the feedback it got during months was incorporated into
the patch in the form of significant improvements, and
at the end of this CF it was at the point that it has really been
polished, and no other feedback was coming.

I'll resubmit.

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

    Alvaro Herrera wrote:

> While I understand that you may think that "silence is consent",
> what I am afraid of is that some committer will look at this two
> months from now and say "I hate this Hcol+ stuff, -1 from me" and
> send the patch back for syntax rework.  IMO it's better to have more
> people chime in here so that the patch that we discuss during the
> next commitfest is really the best one we can think of.

Yes, but on the other hand we can't force people to participate.
If a patch is moving forward and being discussed here between
one author and one reviewer, and nothing particularly wrong
pops out in what is discussed, the reality if that other people will
not intervene.

Besides, as it being mentioned here frequently, all patches, even
much more important ones, are short on reviews and reviewers
and testing, still new stuff must keep getting in the source tree
to progress.

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

    Alvaro Herrera wrote:

> Also, what about the business of putting "x" if there's no third column?
> Three months from now some Czech psql hacker will say "we should use
> Unicode chars for this" and we will be forever stuck with \pset
> unicode_crosstab_marker to change the character to a ☑ BALLOT BOX WITH
> CZECH.  Maybe we should think that a bit harder -- for example, what
> about just rejecting the case with no third column and forcing the user
> to add a third column with the character they choose?  That way you
> avoid that mess.

Yes,  that implicit "X" with 2 column resultsets is not essential, it may
be removed without real damage.

About the possible suggestion to have a \pset unicode_crosstab_marker,
my opinion would be that it's not important enough to justify a new
\pset setting.


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

    Dean Rasheed wrote:

> I don't think we should allow sorting colV values client-side,
> overriding a server-side ORDER BY clause in the query.

I shared that opinion until (IIRC) the v8 or v9 of the patch.
Most of the evolution of this patch has been to go
from no client-side sorting option at all, to the full range
of possibilities, ascending or descending, and in both
vertical and horizontal directions.

I agree that colV sorting can be achieved through the
query's ORDER BY, which additionally is more efficient
so it should be the primary choice.

The reason to allow [+/-]colV in \crosstabview is because
I think the average user will expect it, by symmetry with colH.
As the display is reorganized to be like a "grid" instead of a "list
with several columns", we shift the focus to the symmetry
between horizontal and vertical headers, rather than on
the pre-crosstab form of the resultset, even if it's the
same data.
It's easier for the user to just stick a + in front of a column
reference than to figure out that the same result could
be achieved by editing the query and changing/adding
an ORDER BY.

Or said otherwise, having the [+/-] colV sorting is a way to
avoid the question:
"we can sort the horizontal header, so why can't we sort the
vertical header just the same?"


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

    Dean Rasheed wrote:

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

Note that NULL values in the column that pivots are discarded
by \crosstabview, because NULL as the name of a column does not
make sense.

The doc (in the patch) says:

"The horizontal header, displayed as the first row, contains the set of
all distinct non-null values found in column colH"


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

"Daniel Verite" <daniel@manitou-mail.org> writes:
>     Dean Rasheed wrote:
>> I don't think we should allow sorting colV values client-side,
>> overriding a server-side ORDER BY clause in the query.

> I shared that opinion until (IIRC) the v8 or v9 of the patch.
> Most of the evolution of this patch has been to go
> from no client-side sorting option at all, to the full range
> of possibilities, ascending or descending, and in both
> vertical and horizontal directions.

I haven't been paying attention to this thread ... but it is sure
sounding like this feature has gotten totally out of hand.  Suggest
reconsidering your design goals.

> Or said otherwise, having the [+/-] colV sorting is a way to
> avoid the question:
> "we can sort the horizontal header, so why can't we sort the
> vertical header just the same?"

I would turn that around, and ask why not remove *both* those things.

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.
I'd rather reject the feature altogether than expect that psql is going
to have to grow all of that.
        regards, tom lane

    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

On 2/9/16 8:40 AM, Daniel Verite wrote:
>     Alvaro Herrera wrote:
>
>> While I understand that you may think that "silence is consent",
>> what I am afraid of is that some committer will look at this two
>> months from now and say "I hate this Hcol+ stuff, -1 from me" and
>> send the patch back for syntax rework.  IMO it's better to have more
>> people chime in here so that the patch that we discuss during the
>> next commitfest is really the best one we can think of.
>
> Yes, but on the other hand we can't force people to participate.
> If a patch is moving forward and being discussed here between
> one author and one reviewer, and nothing particularly wrong
> pops out in what is discussed, the reality if that other people will
> not intervene.

The problem is that assumes people are still reading the thread. This is 
a feature I'm very interested, but at some point I just gave up on 
trying to follow it because of the volume of messages. I bet a lot of 
others did the same.

I think in this case, what should have happened is that once an issue 
with the design of the feature itself was identified, a new thread 
should have been started to discuss that part in particular. That would 
have re-raised attention and made it easier for people to follow that 
specific part of the discussion, even if they don't care about some if 
the code intricacies.

> Besides, as it being mentioned here frequently, all patches, even
> much more important ones, are short on reviews and reviewers
> and testing, still new stuff must keep getting in the source tree
> to progress.

Sure, and new stuff will be making it in. The question is: will *your* 
new stuff be making it in?

Believe me, I know how burdensome getting new features pushed is. 
Frankly it shouldn't be this hard, and I certainly don't blame you for 
being frustrated. But none of that changes the fact that the bar for 
including code is very high and if you don't meet it then your stuff 
won't make it in.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 2016-02-09 09:27:04 +0000, Dean Rasheed wrote:
> 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've pretty similar doubts. Addinging features to psql which are complex
enough that it's likely that people will be forced to parse psql
output...  On the other hand, a proper server side solution won't be
easy; so maybe this is a okay enough stopgap.

Greetings,

Andres Freund

On 11 February 2016 at 08:43, Andres Freund <andres@anarazel.de> wrote:
> On 2016-02-09 09:27:04 +0000, Dean Rasheed wrote:
>> 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've pretty similar doubts. Addinging features to psql which are complex
> enough that it's likely that people will be forced to parse psql
> output...  On the other hand, a proper server side solution won't be
> easy; so maybe this is a okay enough stopgap.
>

Well to be clear, I like the idea of this feature, and I'm not trying
to stand in the way of progressing it. However, I can't see myself
committing it in its current form.

My biggest problem is with the sorting, for all the reasons discussed
above. There is absolutely no reason for \crosstabview to be
re-sorting rows -- they should just be left in the original query
result order. Sorting columns is a little more understandable, since
there is no way for the original query to control the order in which
the colV values come out, but Tom raises a good point -- there are far
too many bells and whistles when it comes to sorting, and we don't
want to be adding all of them to the psql syntax.

Thinking about this some more though, perhaps *sorting* the columns is
the wrong way to be thinking about it. Perhaps a better approach would
be to allow the columns to be *listed* (optionally, using a separate
query). Something like the following (don't get too hung up on the
syntax):

SELECT name,      to_char(date, 'Mon') AS month,      sum(amount) AS amountFROM invoicesGROUP BY 1,2ORDER BY name
\crosstabview cols = (select to_char(d, 'Mon') from
generate_series('2000-01-01'::date, '2000-12-01', '1 month') d)

Regards,
Dean

    Alvaro Herrera wrote:

> So please can we have that wiki page so that the syntax can be hammered
> out a bit more.

I've added a wiki page with explanation and examples here:

https://wiki.postgresql.org/wiki/Crosstabview

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

Dean Rasheed wrote:

> My biggest problem is with the sorting, for all the reasons discussed
> above. There is absolutely no reason for \crosstabview to be
> re-sorting rows -- they should just be left in the original query
> result order

We want the option to sort the vertical the header in a late additional
step when the ORDER BY of the query is already assigned to another
purpose.

I've submitted this example on the wiki:
https://wiki.postgresql.org/wiki/Crosstabview

create view v_data as
select * from ( values  ('v1','h2','foo', '2015-04-01'::date),  ('v2','h1','bar', '2015-01-02'),  ('v1','h0','baz',
'2015-07-12'), ('v0','h4','qux', '2015-07-15')) as l(v,h,c,d); 


Normal top-down display:

select v,to_char(d,'Mon') as m, c  from v_data order by d;
v  |  m  |  c
----+-----+-----v2 | Jan | barv1 | Apr | foov1 | Jul | bazv0 | Jul | qux

Crosstabview display without any additional sort:
\crosstabview v m c
v  | Jan | Apr | Jul
----+-----+-----+-----v2 | bar |    | v1 |      | foo | bazv0 |      |    | qux

"d" is not present the resultset but it drives the sort
so that month names come out in the natural order.

\crosstabview does not discard the order of colH nor the order of colV,
it follows both, so that we get v2,v1,v0 in this order in the leftmost
column (vertical header) just like in the resultset.

At this point, it seems to me that it's perfectly reasonable for our user
to expect the possibility of sorting additionally by "v" , without
changing the query and without changing the order of the horizontal
header:
\crosstabview +v m c
v  | Jan | Apr | Jul
----+-----+-----+-----v0 |      |    | quxv1 |      | foo | bazv2 | bar |    |



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

On 2/11/16 4:21 AM, Dean Rasheed wrote:
> Thinking about this some more though, perhaps*sorting*  the columns is
> the wrong way to be thinking about it. Perhaps a better approach would
> be to allow the columns to be*listed*  (optionally, using a separate
> query). Something like the following (don't get too hung up on the
> syntax):
>
> SELECT name,
>         to_char(date, 'Mon') AS month,
>         sum(amount) AS amount
>   FROM invoices
>   GROUP BY 1,2
>   ORDER BY name
> \crosstabview cols = (select to_char(d, 'Mon') from
> generate_series('2000-01-01'::date, '2000-12-01', '1 month') d)

My concern with that is that often you don't know what the columns will 
be, because you don't know what exact data the query will produce. So to 
use this syntax you'd have to re-create a huge chunk of the original 
query. :(
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 15 February 2016 at 14:08, Daniel Verite <daniel@manitou-mail.org> wrote:
>         Dean Rasheed wrote:
>
>> My biggest problem is with the sorting, for all the reasons discussed
>> above. There is absolutely no reason for \crosstabview to be
>> re-sorting rows -- they should just be left in the original query
>> result order
>
> Normal top-down display:
>
> select v,to_char(d,'Mon') as m, c  from v_data order by d;
>
>  v  |  m  |  c
> ----+-----+-----
>  v2 | Jan | bar
>  v1 | Apr | foo
>  v1 | Jul | baz
>  v0 | Jul | qux
>
> At this point, it seems to me that it's perfectly reasonable for our user
> to expect the possibility of sorting additionally by "v" , without
> changing the query and without changing the order of the horizontal
> header:
>
>  \crosstabview +v m c
>
>  v  | Jan | Apr | Jul
> ----+-----+-----+-----
>  v0 |     |     | qux
>  v1 |     | foo | baz
>  v2 | bar |     |
>

I don't find that example particularly compelling. If I want to sort
the rows coming out of a query, my first thought is always going to be
to add/adjust the query's ORDER BY clause, not use some weird +/- psql
syntax.

The crux of the problem here is that in a pivoted query resultset SQL
can be used to control the order of the rows or the columns, but not
both at the same time. IMO it is more natural to use SQL to control
the order of the rows. The columns are the result of the psql
pivoting, so it's reasonable to control them via psql options.

A couple of other points to bear in mind:

The number of columns is always going to be quite limited (at most
1600, and usually far less than that), whereas the number of rows
could be arbitrarily large. So sorting the rows client-side in the way
that you are could get very inefficient, whereas that's not such a
problem for the columns.

The column values are non-NULL, so they require a more limited set of
sort options, whereas the rows could be anything, and people will want
all the sort options to be available.

Regards,
Dean

On 17 February 2016 at 02:32, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 2/11/16 4:21 AM, Dean Rasheed wrote:
>>
>> Thinking about this some more though, perhaps*sorting*  the columns is
>> the wrong way to be thinking about it. Perhaps a better approach would
>> be to allow the columns to be*listed*  (optionally, using a separate
>> query). Something like the following (don't get too hung up on the
>> syntax):
>>
>> SELECT name,
>>         to_char(date, 'Mon') AS month,
>>         sum(amount) AS amount
>>   FROM invoices
>>   GROUP BY 1,2
>>   ORDER BY name
>> \crosstabview cols = (select to_char(d, 'Mon') from
>> generate_series('2000-01-01'::date, '2000-12-01', '1 month') d)
>
>
> My concern with that is that often you don't know what the columns will be,
> because you don't know what exact data the query will produce. So to use
> this syntax you'd have to re-create a huge chunk of the original query. :(
>

Yeah, that's a reasonable concern.

On the flip side, one of the advantages of the above syntax is that
you have absolute control over the columns, whereas with the
sort-based syntax you might find some columns missing (e.g., if there
were no invoices in August) and that could lead to confusion parsing
the results.

I'm not totally opposed to specifying a column sort order in psql, and
perhaps there's a way to support both 'cols' and 'col_order' options
in psql, since there are different situations where one or the other
might be more useful.

What I am opposed to is specifying the row order in psql, because IMO
that's something that should be done entirely in the SQL query.

Regards,
Dean

    Jim Nasby wrote:

> >   ORDER BY name
> > \crosstabview cols = (select to_char(d, 'Mon') from
> > generate_series('2000-01-01'::date, '2000-12-01', '1 month') d)
>
> My concern with that is that often you don't know what the columns will
> be, because you don't know what exact data the query will produce. So to
> use this syntax you'd have to re-create a huge chunk of the original
> query. :(

Also, if that additional query refers to tables, it should be executed
with the same data visibility as the main query. Doesn't that mean
that both queries should happen within the same repeatable
read transaction?

Another  impractical aspect of this approach is that a
meta-command invocation in psql must fit on a single line, so
queries containing newlines are not acceptable as argument.
This problem exists with "\copy (select...) to ..."  already.

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

On 2/9/16 11:21 AM, Daniel Verite wrote:
> Note that NULL values in the column that pivots are discarded
> by \crosstabview, because NULL as the name of a column does not
> make sense.

Why not?

All you're doing is printing it out, and psql is quite capable of
printing a null value.

On 2/17/16 9:03 AM, Dean Rasheed wrote:
> I'm not totally opposed to specifying a column sort order in psql, and
> perhaps there's a way to support both 'cols' and 'col_order' options
> in psql, since there are different situations where one or the other
> might be more useful.

Yeah. If there was some magic way to reference the underlying data with 
your syntax it probably wouldn't be that bad. AIUI normally we're just 
dumping data into a Portal and there's no option to read back from it, 
but if the query results were first put in a tuplestore then I suspect 
it wouldn't be that hard to query against it and produce another result set.

> What I am opposed to is specifying the row order in psql, because IMO
> that's something that should be done entirely in the SQL query.

+1
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

    Daniel Verite wrote:

> > >   ORDER BY name
> > > \crosstabview cols = (select to_char(d, 'Mon') from
> > > generate_series('2000-01-01'::date, '2000-12-01', '1 month') d)
> >
> > My concern with that is that often you don't know what the columns will
> > be, because you don't know what exact data the query will produce. So to
> > use this syntax you'd have to re-create a huge chunk of the original
> > query. :(
>
> Also, if that additional query refers to tables, it should be executed
> with the same data visibility as the main query. Doesn't that mean
> that both queries should happen within the same repeatable
> read transaction?
>
> Another  impractical aspect of this approach is that a
> meta-command invocation in psql must fit on a single line, so
> queries containing newlines are not acceptable as argument.
> This problem exists with "\copy (select...) to ..."  already.

Thinking more about that, it occurs to me that if the sort must come
from a user-supplied bit of SQL, it would be simpler to just direct the
user to submit it in the main query, in an additional dedicated column.

For instance, to get a specific, separate order on "h",
let the user change this:
 SELECT v, h, c FROM v_data ORDER BY v;

into that:
 SELECT v, h, row_number() over(order by h) as hn, c  FROM v_data ORDER BY v;

then with a relatively simple modification to the patch,
this invocation:
\crosstabview v h:hn c

would display "h" in the horizontal header ordered by "hn".

ISTM this handles two objections raised upthread:

1. The ORDER BY inside OVER() can be augmented with additional
clauses such as lc_collate, desc, nulls last, etc... contrary to
the controversed "+/-" syntax.

2. a post-sort "backdoor" query is no longer necessary.

The drawback for me is that this change doesn't play out with
my original scenario for the command, which is to give the ability to
scrutinize query results in crosstab mode, playing with variations on
what column is pivoted and how headers for both directions get sorted,
while ideally not changing _at all_ the original query in the query
buffer, but just invoking  successive \crosstabview [args] commands
with varying arguments.


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

    Peter Eisentraut wrote:

> On 2/9/16 11:21 AM, Daniel Verite wrote:
> > Note that NULL values in the column that pivots are discarded
> > by \crosstabview, because NULL as the name of a column does not
> > make sense.
>
> Why not?
>
> All you're doing is printing it out, and psql is quite capable of
> printing a null value.

Initially it's by analogy with the crosstab SRF, but it's true
that the same principle does not have to apply to crosstabview.

The code could set in the header whatever text "pset null" is set to,
at the place where a pivoted NULL would be supposed to go
if it was not filtered out in the first place.

I'll consider implementing that change if there's no objection.

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

    Dean Rasheed wrote:

> If I want to sort the rows coming out of a query, my first thought
> is always going to be to add/adjust the query's ORDER BY clause, not
> use some weird +/- psql syntax.

About the vertical sort, I agree on all your points.
It's best to rely on ORDER BY for all the reasons mentioned,
as opposed to a separate sort in a second step.

But you're considering the case when a user is designing
or adapting a query for the purpose of crosstab
viewing. As mentioned in my previous reply (about the
methods to achieve horizontal sort), that scenario is not really
what motivates the feature in the first place.

If removing that sort option is required to move forward
with the patch because it's controversial, so be it,
but overall I don't see this as a benefit for the end user,
it's just an option.

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

On Thu, Feb 18, 2016 at 9:23 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
>         Dean Rasheed wrote:
>
>> If I want to sort the rows coming out of a query, my first thought
>> is always going to be to add/adjust the query's ORDER BY clause, not
>> use some weird +/- psql syntax.
>
> About the vertical sort, I agree on all your points.
> It's best to rely on ORDER BY for all the reasons mentioned,
> as opposed to a separate sort in a second step.
>
> But you're considering the case when a user is designing
> or adapting a query for the purpose of crosstab
> viewing. As mentioned in my previous reply (about the
> methods to achieve horizontal sort), that scenario is not really
> what motivates the feature in the first place.
>
> If removing that sort option is required to move forward
> with the patch because it's controversial, so be it,
> but overall I don't see this as a benefit for the end user,
> it's just an option.

Discussion on this patch seems to have died off.  I'm probably not
going to win any popularity contests for saying this, but I think we
should reject this patch.  I don't feel like this is really a psql
feature: it's a powerful data visualization tool which we're proposing
to jam into psql.  I don't think that's psql's purpose.  I also think
it's quite possible that there could be an unbounded number of
slightly different things that people want here, and if we take this
one and a couple more, the code for these individual features could
come to be larger than all of psql, even though probably 95% of psql
users would never use any of those.

Now, that having been said, if other people want this feature to go in
and are willing to do the work to get it in, I've said my piece and
won't complain further.  There are a couple of committers who have
taken positive interest in this thread, so that's good.  However,
there are also a couple of committers who have expressed doubts
similar to mine, so that's not so good.  But worse than either of
those things, there is no real agreement on what the overall design of
this feature should be.  Everybody wants something a little different,
for different reasons.  If we can't come to an agreement, more or less
immediately, on what to try to get into 9.6, then this can't go into
this release.  Whether it should go into a future release is a
question we can leave for another time.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

    Robert Haas wrote:

> But worse than either of  those things, there is no real
> agreement on what the overall design of this feature
> should be.

The part in the design that raised concerns upthread is
essentially how headers sorting is exposed to the user and
implemented.

As suggested in [1], I've made some drastic changes in the
attached patch to take the comments (from Dean R., Tom L.)
into account. The idea is to limit to the bare minimum
the involvement of psql in sorting:

- the +/- syntax goes away

- the possibility of post-sorting the values through a backdoor
  query goes away too, for both headers.

- the vertical order of the crosstab view is now driven solely by the
  order  in the query

- the order of the horizontal header can be optionally specified
  by a column expected to contain an integer, with the syntax
  \crosstabview colv colh:scolh [other cols]
  which means "colh" will be sorted by "scolh".
  It still defaults to whatever order "colh" comes in from the results

  Concerning the optional "scolh", there are cases where it might pre-exist
  naturally, such as a month number going in pair with a month name.
  In other cases,  a user may add it as a kind of "synthetic column"
  by way of a window function, for example:
    SELECT ...other columns...,
       (row_number() over(order by something [order options]) as scolh
   FROM...
   Only the relative order of scolh values is taken into account, the value
itself
   has no meaning for crosstabview.

- also NULLs are no longer excluded from headers, per Peter E.
  comment in [2].


[1]
http://www.postgresql.org/message-id/3d513263-104b-41e3-b1c7-4ad4bd99c491@mm

[2] http://www.postgresql.org/message-id/56C4E344.6070903@gmx.net


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

On 3/13/16 12:48 AM, Pavel Stehule wrote:
> crosstabview is really visualization tool. **But now, there are not any
> other tool available from terminal.** So this can be significant help to
> all people who would to use this functionality.

Not just the terminal either. Offhand I'm not aware of *any* fairly 
simple tool that provides crosstab. There's a bunch of 
complicated/expensive BI tools that do, but unless you've gone through 
the trouble of getting one of those setup you're currently pretty stuck.

Ultimately I'd really like some way to remove/reduce the restriction of 
result set definitions needing to be determined at plan time. That would 
open the door for server-side crosstab/pivot as well a a host of other 
things (such as dynamically turning a hstore/json/xml field into a 
recordset).
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby wrote:
> On 3/13/16 12:48 AM, Pavel Stehule wrote:
> >crosstabview is really visualization tool. **But now, there are not any
> >other tool available from terminal.** So this can be significant help to
> >all people who would to use this functionality.
> 
> Not just the terminal either. Offhand I'm not aware of *any* fairly simple
> tool that provides crosstab. There's a bunch of complicated/expensive BI
> tools that do, but unless you've gone through the trouble of getting one of
> those setup you're currently pretty stuck.

I'm definitely +1 for this feature in psql also.

Some years ago we had a discussion about splitting psql in two parts, a
bare-bones one which would help script-writing and another one with
fancy features; we decided to keep one tool to rule them all and made
the implicit decision that we would grow exotic, sophisticated features
into psql.  ISTM that this patch is going in that direction.

> Ultimately I'd really like some way to remove/reduce the restriction of
> result set definitions needing to be determined at plan time. That would
> open the door for server-side crosstab/pivot as well a a host of other
> things (such as dynamically turning a hstore/json/xml field into a
> recordset).

That seems so far down the road that I don't think it should block the
psql feature being proposed in this thread, but yes I would like that
one too.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Jim Nasby wrote:

> Ultimately I'd really like some way to remove/reduce the restriction of
> result set definitions needing to be determined at plan time. That would
> open the door for server-side crosstab/pivot as well a a host of other
> things (such as dynamically turning a hstore/json/xml field into a
> recordset).

> Ultimately I'd really like some way to remove/reduce the restriction of
> result set definitions needing to be determined at plan time. That would
> open the door for server-side crosstab/pivot as well a a host of other
> things (such as dynamically turning a hstore/json/xml field into a
> recordset).

That would go against a basic expectation of prepared statements, the
fact that queries can be parsed/prepared without any part of them
being executed.

For a dynamic pivot, but probably also for the other examples you
have in mind, the SQL engine wouldn't be able to determine the output
columns without executing a least a subselect to look inside some
table(s).

I suspect that the implications of this would be so far reaching and
problematic that it will just not happen.

It seems to me that a dynamic pivot will always consist of
two SQL queries that can never be combined into one,
unless using a workaround à la Oracle, which encapsulates the
entire dynamic resultset into an XML blob as output.
The problem here being that the client-side tools
that people routinely use are not equipped to process it anyway;
at least that's what  I find by anecdotal evidence for instance in:
https://community.oracle.com/thread/2133154?tstart=0or
http://stackoverflow.com/questions/19298424or
https://community.oracle.com/thread/2388982?tstart=0


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

On Sat, Mar 12, 2016 at 10:34 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
>> But worse than either of  those things, there is no real
>> agreement on what the overall design of this feature
>> should be.
>
> The part in the design that raised concerns upthread is
> essentially how headers sorting is exposed to the user and
> implemented.
>
> As suggested in [1], I've made some drastic changes in the
> attached patch to take the comments (from Dean R., Tom L.)
> into account.
> [ ... lengthy explanation ... ]
> - also NULLs are no longer excluded from headers, per Peter E.
>   comment in [2].

Dean, Tom, Peter, what do you think of the new version?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Mar 14, 2016 at 2:55 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Mar 12, 2016 at 10:34 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
>>> But worse than either of  those things, there is no real
>>> agreement on what the overall design of this feature
>>> should be.
>>
>> The part in the design that raised concerns upthread is
>> essentially how headers sorting is exposed to the user and
>> implemented.
>>
>> As suggested in [1], I've made some drastic changes in the
>> attached patch to take the comments (from Dean R., Tom L.)
>> into account.
>> [ ... lengthy explanation ... ]
>> - also NULLs are no longer excluded from headers, per Peter E.
>>   comment in [2].
>
> Dean, Tom, Peter, what do you think of the new version?

Is anyone up for re-reviewing this?  If not, I think we're going to
have to reject this for lack of interest.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Pavel Stehule wrote:
> Can I do review?

Of course.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Sun, Mar 20, 2016 at 5:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> From my perspective, it is ready for commiter. Daniel solved the most big
> issues.

OK, so that brings us back to: is there any committer who likes this
enough to want to look at committing it?  My view hasn't changed much
since http://www.postgresql.org/message-id/CA+TgmoZ4yAduq9j8XTGRBh868JH2nj_NW_qgkXB32CeDsVTy0w@mail.gmail.com

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:
> On Sun, Mar 20, 2016 at 5:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> > From my perspective, it is ready for commiter. Daniel solved the most big
> > issues.
> 
> OK, so that brings us back to: is there any committer who likes this
> enough to want to look at committing it?  My view hasn't changed much
> since http://www.postgresql.org/message-id/CA+TgmoZ4yAduq9j8XTGRBh868JH2nj_NW_qgkXB32CeDsVTy0w@mail.gmail.com

I volunteer for that, but it'll be a few days so if anyone else is
interested, feel free.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I've been looking at this patch.  First thing was to rebase on top of
recent psql code restructuring; second, pgindent; third, reordered the
code in crosstabview.c more sensibly (had to add prototypes).  New
version attached.

Then I looked at the docs to try to figure out exactly how it works.
I'm surprised that there's not a single example added to the psql
manpage.  Please add one.

I then tested it a bit, "kick the tires" so to speak.  I noticed that
error handling is broken.  For instance, observe the query prompt after
the error:

regression=# select * from pg_class \crosstabview relnatts
\crosstabview: missing second argument
regression-#

At this point the query buffer contains the query (you can see it with
\e), which seems bogus to me.  The query buffer needs to be reset.
Compare \gexec:
alvherre=# select 1 \gexec
ERROR:  error de sintaxis en o cerca de «1»
LÍNEA 1: 1
         ^
alvherre=#


Also, using bogus column names as arguments cause state to get all
bogus:

alvherre=# select * from pg_class \crosstabview relnatts relkinda
Invalid column name: relkinda
alvherre=# select 1;
The query must return at least two columns to be shown in crosstab

Note that the second query is not crosstab at all, yet the error message
is entirely bogus.  This one is probably the same bug:

alvherre=# select 'one', 'two';
Invalid column name: relnatts

Apparently, once in that state, not even a successful query crosstab
display resets the state correctly:

alvherre=# select * from pg_class \crosstabview relnatts relkinda
Invalid column name: relkinda
alvherre=# select 'one' as relnatts, 'two' as relkinda \crosstabview
 relnatts | two
----------+-----
 one      | X
(1 fila)

alvherre=# select 1;
The query must return at least two columns to be shown in crosstab

Please fix this.


Some additional items:

* A few examples in docs.  The psql manpage should have at least two new
examples showing the crosstab features, one with the simplest case you
can think of, and another one showing all the features.

* Add regression test cases somewhere for the regression database.
Probably use "FROM tenk1 WHERE hundred < 5", which provides you with 500
rows, enough for many interesting games.  Make sure to test all the
provided features.  I would use a new psql.sql file for this.

* How did you come up with the 1600 value?  Whatever it is, please use a
#define instead of hardcoding it.

* In the "if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0')"
block (line 497 in the attached), can't we do the same thing by using
psprintf?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Alvaro Herrera wrote:


Thanks for looking into that patch!

> regression=# select * from pg_class \crosstabview relnatts
> \crosstabview: missing second argument
> regression-#

Fixed. This was modelled after the behavior of:
 select 1 \badcommand
but I've changed to mimic what happens with:
  select 1 \g /some/invalid/path
the query buffer is not discarded by the error but the prompt
is ready for a fresh new command.

> alvherre=# select * from pg_class \crosstabview relnatts relkinda
> Invalid column name: relkinda
> alvherre=# select 1;
> The query must return at least two columns to be shown in crosstab

Definitely a bug. Fixed.

Also fixed a one-off bug with quoted columns: in parseColumnRefs(),
first call to PQmblen(), I wrongly assumed that
PQmblen("", ..) returns 0, whereas in fact it returns 1.

> * A few examples in docs.  The psql manpage should have at least two new
> examples showing the crosstab features, one with the simplest case you
> can think of, and another one showing all the features.

Added that in the EXAMPLES section at the very end of the manpage.

> * Add regression test cases somewhere for the regression database.
> Probably use "FROM tenk1 WHERE hundred < 5", which provides you with 500
> rows, enough for many interesting games.  Make sure to test all the
> provided features.  I would use a new psql.sql file for this.

Looking into regression tests, not yet done.

> * How did you come up with the 1600 value?  Whatever it is, please use a
> #define instead of hardcoding it.

Done with accompanying comment in crosstabview.h

> * In the "if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0')"
> block (line 497 in the attached), can't we do the same thing by using
> psprintf?

In that block, we can't pass a cell contents as a valist and be done with
that cell, because duplicates of (col value,row value) may happen
at any iteration of the upper loop over PQntuples(results). Any cell really
may need reallocation unpredictably until that loop is done, whereas
psprintf starts by allocating a new buffer unconditionally, so it doesn't
look
to me like it could help to simplify that block.


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

Daniel Verite wrote:

> > regression=# select * from pg_class \crosstabview relnatts
> > \crosstabview: missing second argument
> > regression-#
>
> Fixed. This was modelled after the behavior of:
>  select 1 \badcommand
> but I've changed to mimic what happens with:
>   select 1 \g /some/invalid/path
> the query buffer is not discarded by the error but the prompt
> is ready for a fresh new command.

Works for me.

> > * A few examples in docs.  The psql manpage should have at least two new
> > examples showing the crosstab features, one with the simplest case you
> > can think of, and another one showing all the features.
>
> Added that in the EXAMPLES section at the very end of the manpage.

Ok.  Seems a bit too short to me, and I don't like the fact that you
can't actually run it because you need to create the my_table
beforehand.  I think it'd be better if you used a VALUES clause there,
so that the reader can cut'n paste into psql to start to play with the
feature.

> > * In the "if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0')"
> > block (line 497 in the attached), can't we do the same thing by using
> > psprintf?
>
> In that block, we can't pass a cell contents as a valist and be done with
> that cell, because duplicates of (col value,row value) may happen
> at any iteration of the upper loop over PQntuples(results). Any cell really
> may need reallocation unpredictably until that loop is done, whereas
> psprintf starts by allocating a new buffer unconditionally, so it doesn't
> look to me like it could help to simplify that block.

I don't know what you mean, but here's what I meant.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I wonder if the business of appending values of multiple columns
separated with spaces is doing us any good.  Why not require that
there's a single column in the cell?  If the user wants to put things
together, they can use format() or just || the fields together.  What
benefit is there to the ' '?  When I ran my first test queries over
pg_class I was surprised about this behavior:

alvherre=# select * from pg_class
alvherre=# \crosstabview relnatts relkind
relnatts |                                                                       r
                                 |                                               t
         |                                                        i
   |                                                                           v
                                  
 

----------+------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------
    26 | pg_statistic 11 11397 0 10 0 2619 0 15 380 15 2840 t f p 0 f f f f f f f t n 540 1 {alvherre=arwdDxt/alvherre}
(null)                         |
       |
 |       30 | pg_type 11 71 0 10 0 0 0 9 358 9 0 t f p 0 t f f f f f f t n 540 1 {=r/alvherre} (null)
                                    |
            |
      |        3 | pg_user_mapping 11 11633 0 10 0 1418 0 0 0 0 0 t f p 0 t f f f f f f t n 540 1
{alvherre=arwdDxt/alvherre}(null)                             +| pg_toast_2604 99 11642 0 10 0 2830 0 0 0 0 0 t f p 0 f
ff f f f f t n 540 1 (null) (null)    +| pg_amop_opr_fam_index 11 0 0 10 403 2654 0 5 688 0 0 f f p 0 f f f f f f f t n
00 (null) (null)                +| pg_group 11 11661 0 10 0 11660 0 0 0 0 0 f f p 0 f f t f f f f t n 0 0 {=r/alvherre}
(null)                                                         +
 


I'm tempted to rip that out, unless you have a reason not to.

In fact, I think even the grouping of values of multiple rows with \n is
not terribly great either.  Why not just require people to group the
values beforehand?  You can use "string_agg(column, E'\n')" to get the
same behavior, plus you can do other things such as sum() etc.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera wrote:
> Daniel Verite wrote:

> > > * A few examples in docs.  The psql manpage should have at least two new
> > > examples showing the crosstab features, one with the simplest case you
> > > can think of, and another one showing all the features.
> > 
> > Added that in the EXAMPLES section at the very end of the manpage.
> 
> Ok.  Seems a bit too short to me, and I don't like the fact that you
> can't actually run it because you need to create the my_table
> beforehand.  I think it'd be better if you used a VALUES clause there,
> so that the reader can cut'n paste into psql to start to play with the
> feature.

Oh, I noticed now that my_table was created by previous examples.
Nevermind.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Daniel Verite wrote:

> > * In the "if (cont.cells[idx] != NULL && cont.cells[idx][0] != '\0')"
> > block (line 497 in the attached), can't we do the same thing by using
> > psprintf?
>
> In that block, we can't pass a cell contents as a valist and be done with
> that cell, because duplicates of (col value,row value) may happen
> at any iteration of the upper loop over PQntuples(results). Any cell really
> may need reallocation unpredictably until that loop is done, whereas
> psprintf starts by allocating a new buffer unconditionally, so it doesn't
> look
> to me like it could help to simplify that block.

I messed with that code some more, as it looked unnecessarily
complicated; please see attached and verify that it still behaves
sanely.  This needs those regression tests you promised.  I tested a few
cases and it seems good to me.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Alvaro Herrera wrote:

> I messed with that code some more, as it looked unnecessarily
> complicated; please see attached and verify that it still behaves
> sanely.  This needs those regression tests you promised.  I tested a few
> cases and it seems good to me.

I've fixed a couple things over v16:
- avoid passing every cell through psprintf, which happened due
  to cont.cells being pre-initialized to empty strings.
- adjusted the loop freeing allocated_cells

and added the regression tests.

Attached is the diff over v16, tested with make check and valgrind.

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

    Alvaro Herrera wrote:

> I wonder if the business of appending values of multiple columns
> separated with spaces is doing us any good.  Why not require that
> there's a single column in the cell?  If the user wants to put things
> together, they can use format() or just || the fields together.  What
> benefit is there to the ' '?  When I ran my first test queries over
> pg_class I was surprised about this behavior:
> alvherre=# select * from pg_class
> alvherre=# \crosstabview relnatts relkind

ISTM that this could be avoided by erroring out for lack of an
explicit 3rd column as argument. IOW, we wouldn't assume
that "no column specified" means "show all columns".

About simply ripping out the possibility of having multiple
columns into cells, it's more radical but if that part turns out to
be more confusing than useful, I don't have a problem
with removing it.

The other case of stringing multiple contents into the same cell
is when different tuples carry (row,column) duplicates.
I'm not inclined to disallow that case, I think it would go too far
in guessing what the user expects.
My expectation for a viewer is that it displays the results as far as
possible, whatever they are.
Also, showing such contents in vertically-growing cells as it
does now allows the user to spot these easily in the grid when
they happen to be outliers. I'm seeing it as useful in that case.

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

On Fri, Apr 8, 2016 at 7:23 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
>         Alvaro Herrera wrote:
>
>> I wonder if the business of appending values of multiple columns
>> separated with spaces is doing us any good.  Why not require that
>> there's a single column in the cell?  If the user wants to put things
>> together, they can use format() or just || the fields together.  What
>> benefit is there to the ' '?  When I ran my first test queries over
>> pg_class I was surprised about this behavior:
>> alvherre=# select * from pg_class
>> alvherre=# \crosstabview relnatts relkind
>
> ISTM that this could be avoided by erroring out for lack of an
> explicit 3rd column as argument. IOW, we wouldn't assume
> that "no column specified" means "show all columns".
>
> About simply ripping out the possibility of having multiple
> columns into cells, it's more radical but if that part turns out to
> be more confusing than useful, I don't have a problem
> with removing it.
>
> The other case of stringing multiple contents into the same cell
> is when different tuples carry (row,column) duplicates.
> I'm not inclined to disallow that case, I think it would go too far
> in guessing what the user expects.
> My expectation for a viewer is that it displays the results as far as
> possible, whatever they are.
> Also, showing such contents in vertically-growing cells as it
> does now allows the user to spot these easily in the grid when
> they happen to be outliers. I'm seeing it as useful in that case.

This seems like it might be converging on some sort of consensus, but
I'm wondering if we shouldn't push it to 9.7, instead of rushing
decisions that we will later have trouble changing on
backward-compatibility grounds.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

> This seems like it might be converging on some sort of consensus, but
> I'm wondering if we shouldn't push it to 9.7, instead of rushing
> decisions that we will later have trouble changing on
> backward-compatibility grounds.

My intention is to commit this afternoon in the next couple of hours,
and only the most basic case is going to be supported, and the rest of
the cases (concatenation of several fields and several rows, etc) are
just going to throw errors; that way, it will be easy to add more
features later as they are agreed upon.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Daniel Verite wrote:

> ISTM that this could be avoided by erroring out for lack of an
> explicit 3rd column as argument. IOW, we wouldn't assume
> that "no column specified" means "show all columns". 
> 
> About simply ripping out the possibility of having multiple
> columns into cells, it's more radical but if that part turns out to
> be more confusing than useful, I don't have a problem
> with removing it.

Okay, I've ripped that out since I wasn't comfortable with the general
idea.  Once you have two data values for the same cell, the new code
raises an error, indicating the corresponding vertical and horizontal
header values; that way it's easy to spot where the problem is.

I also removed the FETCH_COUNT bits; it didn't make a lot of sense to
me.  Like \gexec, the query is executed to completion when in
\crosstabview regardless of FETCH_COUNT.

> The other case of stringing multiple contents into the same cell
> is when different tuples carry (row,column) duplicates.
> I'm not inclined to disallow that case, I think it would go too far
> in guessing what the user expects.
> My expectation for a viewer is that it displays the results as far as
> possible, whatever they are. 

The reason I made this case throw an error is that we can tweak the
behavior later.  I think separating them with newlines is too cute and
will be unusable when you have values that have embedded newlines; you
can imitate that behavior with string_agg(val, E'\n') as I've done in
the regression tests.  One option for improving it would be to have it
add another record, but that requires shifting the values of all cells
by the number of columns (you can see that if you change the border
options, or in HTML output etc).  We can do that later.

> Also, showing such contents in vertically-growing cells as it
> does now allows the user to spot these easily in the grid when
> they happen to be outliers. I'm seeing it as useful in that case.

It's useful, no doubt.

I pushed it.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Alvaro Herrera wrote:

> I pushed it.

That's awesome, thanks! Also thanks to Pavel who reviewed and helped
continuously when iterating on this feature, and all others who
participed in this thread.

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

Re: Alvaro Herrera 2016-04-09 <20160408232553.GA721890@alvherre.pgsql>
> It's useful, no doubt.

It's cool :)

> I pushed it.

Here's a small doc patch that removes the bogus space in "colH [:scolH]"
(otherwise psql complains that it is ignoring the 4th parameter.

It also adds an index entry and adds a note to the old crosstab
functions to make people aware of \crosstabview.

Christoph

Re: To PostgreSQL Hackers 2016-04-13 <20160413092312.GA21485@msg.df7cb.de>
> Re: Alvaro Herrera 2016-04-09 <20160408232553.GA721890@alvherre.pgsql>
> > It's useful, no doubt.
>
> It's cool :)
>
> > I pushed it.
>
> Here's a small doc patch that removes the bogus space in "colH [:scolH]"
> (otherwise psql complains that it is ignoring the 4th parameter.
>
> It also adds an index entry and adds a note to the old crosstab
> functions to make people aware of \crosstabview.

I should save before diffing, here's the version I actually wanted to
submit ...

Christoph

Christoph Berg <myon@debian.org> writes:
>> Here's a small doc patch that removes the bogus space in "colH [:scolH]"
>> (otherwise psql complains that it is ignoring the 4th parameter.
>> It also adds an index entry and adds a note to the old crosstab
>> functions to make people aware of \crosstabview.

> I should save before diffing, here's the version I actually wanted to
> submit ...

Hm, we do not have <indexterm> entries attached to any other psql
meta-commands.  Maybe they all should have one, or maybe not, but
I'm unconvinced about adding one for just this command.  What I did
instead was to make a link target (which there *is* precedent for,
see \copy) and have the note in tablefunc.sgml link to it.

I failed to resist the temptation to edit the command description
rather heavily, too.

Pushed with revisions.
        regards, tom lane

Re: Tom Lane 2016-04-13 <1854.1460562787@sss.pgh.pa.us>
> Hm, we do not have <indexterm> entries attached to any other psql
> meta-commands.  Maybe they all should have one, or maybe not, but
> I'm unconvinced about adding one for just this command.  What I did
> instead was to make a link target (which there *is* precedent for,
> see \copy) and have the note in tablefunc.sgml link to it.

Hmm. I was looking at \? first and because the 1-liner there isn't
even attempting to explain the \crosstabview syntax, went on to
bookindex.html, but could only find the "wrong" crosstab there.

\copy is the other example where I'd think a bookindex entry would
make sense so people can look up the documentation, all the other
\backslash things are much easier to grasp.

Not sure if it's only me, but bookindex.html is where I have my
browser bookmark to start diving into the documentation.

> I failed to resist the temptation to edit the command description
> rather heavily, too.
> 
> Pushed with revisions.

Thanks!


Another thing about \crosstabview:

# select 1,2 \crosstabview
The query must return at least two columns to be shown in crosstab

s/two/three/, I guess.

Christoph

Christoph Berg <myon@debian.org> writes:
> Another thing about \crosstabview:

> # select 1,2 \crosstabview
> The query must return at least two columns to be shown in crosstab

> s/two/three/, I guess.

Yeah, I noticed that.  See
http://www.postgresql.org/message-id/10276.1460569515@sss.pgh.pa.us
        regards, tom lane

David G. Johnston wrote:

> "​
>  crosstabview with only 2 output columns
> ​ "​
> 
> ​https://wiki.postgresql.org/wiki/Crosstabview
> ​(last section on that page)
> 
> ​never got implemented....

It was implemented in Daniel's patch.  I removed it before commit and
failed to notice the reference in the docs and help.  (The reason I
removed it is that it seemed to me that hardcoding it to the character X
was going to become a nuisance in the future -- I mean why not ☒ ?
It's simple to do at the query level by having the character as a
literal in the third column.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    David G. Johnston wrote:

> > ​So I guess:
>
> "​
>  crosstabview with only 2 output columns
> ​ "​
>
> ​https://wiki.postgresql.org/wiki/Crosstabview
> ​(last section on that page)
>
> ​never got implemented....

It was implemented but eventually removed.
I will update shortly this wiki page to reflect the status of the feature
as commited.  ATM it features what was still under review a few weeks
ago, and it changed quite a bit eventually, notably on the point you
mention and on the sort capability.


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