Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Fri, Apr 8, 2016 at 7:23 AM, Daniel Verite 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Thu, Apr 7, 2016 at 1:26 PM, 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 >

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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 '

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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.

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Robert Haas wrote: > On Sun, Mar 20, 2016 at 5:27 PM, Pavel Stehule > 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Sun, Mar 20, 2016 at 5:27 PM, Pavel Stehule 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Hi 2016-03-19 16:31 GMT+01:00 Alvaro Herrera : > Pavel Stehule wrote: > > > Can I do review? > > Of course. > I did review of last patch. I had to do small changes to run the code due last Tom's changes in psql. Updated patch is attached. The last changes in this

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Pavel Stehule wrote: > Can I do review? Of course. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

2016-03-19 15:45 GMT+01:00 Robert Haas : > On Mon, Mar 14, 2016 at 2:55 PM, Robert Haas > wrote: > > On Sat, Mar 12, 2016 at 10:34 AM, Daniel Verite > wrote: > >>> But worse than either of those things, there is no real >

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Mon, Mar 14, 2016 at 2:55 PM, Robert Haas wrote: > On Sat, Mar 12, 2016 at 10:34 AM, Daniel Verite > wrote: >>> But worse than either of those things, there is no real >>> agreement on what the overall design of this feature >>> should be. >>

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Sat, Mar 12, 2016 at 10:34 AM, Daniel Verite 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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.

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

2016-03-11 14:49 GMT+01:00 Robert Haas : > On Thu, Feb 18, 2016 at 9:23 AM, Daniel Verite > 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On Thu, Feb 18, 2016 at 9:23 AM, Daniel Verite 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 17 February 2016 at 02:32, Jim Nasby 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 15 February 2016 at 14:08, Daniel Verite 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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:

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 2016-02-09 09:27:04 +, 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 11 February 2016 at 08:43, Andres Freund wrote: > On 2016-02-09 09:27:04 +, 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Hi Looking at this patch, I have mixed feelings about it. On the one hand > I really like the look of the output, and I can see that the non-fixed > nature of the output columns makes this hard to achieve server-side. > > But on the other hand, this seems to be going way beyond the normal > level

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 9 February 2016 at 05:24, Pavel Stehule 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).

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 9 February 2016 at 10:09, Pavel Stehule 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

> > > > SELECT name, to_char(date, 'mon') AS month, extract(month from date) AS > > month_order, sum(amount) AS amount FROM invoices GROUP BY 1,2,3; > > > > and crosstabview command (per Daniel proposal) > > > > \crosstabview +name +month:month_order amount > > > > But if I don't need column

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

On 9 February 2016 at 11:06, Pavel Stehule 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

"Daniel Verite" 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Hi I tested last version, v11 and I have not any objection It is working as expected all regress tests passed, there is related documentation and new test is attached. This patch is ready form commiter. Daniel, thank you very much, it is interesting feature. Regards Pavel

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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.

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Hi 2016-01-22 19:53 GMT+01:00 Daniel Verite : > 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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

Hi 2015-12-23 21:36 GMT+01:00 Daniel Verite : >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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

2015-12-23 21:36 GMT+01:00 Daniel Verite : >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

Re: [HACKERS] [patch] Proposal for \crosstabview in psql

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