Re: [patch] A \pivot command for psql

On Tue, Aug 11, 2015 at 10:13:48AM -0400, Robert Haas wrote:
> On Sun, Aug 9, 2015 at 8:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > psql is a minority API, you know.
>
> Not for me. psql has already got a bunch of bells and whistles to
> format things in particular ways that people have wanted, and I'm not
> really sure why the bar for this proposal should be any higher. Is
> this less useful than \pset linestype unicode, complete with trying to
> auto-detect whether to enable the feature? Than automatically
> switching between expanded mode and regular mode based on the width of
> the TTY? I considered those features rather frivolous, but it seemed
> to me that their inclusion was widely supported and that a number of
> people were really quite happy about them.
>
> I guess you could argue that those are inherently client-side features
> and this is not, and I'll grant that point. But a client-side feature
> in 9.5

9.5?!? I hope you meant 9.6 because if 9.5 is still open for new
features, we're going to have to explain to all the people who thought
they missed the cut-off why this one is jumping the line in front of
them.

> beats a server-side feature in 10.6 every day of the week.

Having done some of the preliminary research into a server-side
implementation, I see it a different way.

First, this is more of a 9.6 feature than a 10.6, at least in terms of
the first cut functionality, and I don't see good reasons why the
performance would need to be terrible in the first cut. In
particular, the PIVOT case looks like it could pretty easily use the
FILTER machinery directly, while the UNPIVOT case could use what
LATERAL does. Doubtless, further optimizations are possible for each,
and for interesting sub-cases, but that's a project for later, as
performance optimizations should be.

Second, if we put this feature as-is in psql, we're stuck supporting
it in psql until the end of time, even if (when, I believe) we have a
fuller and likely not perfectly compatible feature on the back-end.

That said, a thing in psql that could slice serialized output into
columns would be handy as a broad, general part of reporting in psql,
and would mesh quite nicely with a back-end PIVOT (SERIALIZATION FOO)
or whatever syntax we land on.

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

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