Re: [patch] A \pivot command for psql

On Mon, Aug 10, 2015 at 07:10:41PM +0200, Daniel Verite wrote:
> David Fetter wrote:
>
> > I'm working up a proposal to add (UN)PIVOT support to the back-end.
>
> I was under the impression that a server-side PIVOT *with dynamic
> columns* was just unworkable as an SQL query, because it couldn't be
> prepared if it existed.

That depends on what you mean by "dynamic columns." The approach
taken in the tablefunc extension is to use functions which return
SETOF RECORD, which in turn need to be cast at runtime.

At least one other implementation takes two separate approaches, both
interesting at least from my point of view.

The first is to spell out all the columns in the query, which is not
"dynamic columns" in any reasonable sense, as "hand-craft one piece of
SQL whose purpose is to generate the actual pivot SQL" is not
a reasonable level of dynamic.

The second, more on point, is to specify a serialization for the rows
in the "dynamic columns" case. Their syntax is "PIVOT XML", but I
would rather do something more like "PIVOT (SERIALIZATION XML)".

A third idea I have only roughed out feels a bit like cheating:
creating a function which returns two distinct rowtypes via REFCURSORs
or similar. The first result, used to create a CAST, spells out the
row type of the second.

> I am wrong on that? I feel like you guys are all telling me that
> \pivot should happen on the server, but the point that it would not
> be realistic to begin with is not considered.

I think that starting the conversation again, especially at this stage
of the 9.6 cycle, is a very good thing, whatever its outcome.

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