Re: [patch] A \pivot command for psql

From: David Fetter <david(at)fetter(dot)org>
To: Daniel Verite <daniel(at)manitou-mail(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [patch] A \pivot command for psql
Date: 2015-08-11 16:54:11
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Aug 11, 2015 at 05:13:03PM +0200, Daniel Verite wrote:
> David Fetter wrote:
> > 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.
> For me, "PIVOT with dynamic columns" would be a pivot query
> whose output columns are not enumerated as input in the
> SQL query itself, in any form.

I'm pretty sure people will want to be able to specify them in some
form. On one implementation, it looks like:

select * from (
select times_purchased as "Purchase Frequency", state_code
from customers t
pivot xml
for state_code in (select state_code from preferred_states)
order by 1

Another basically punts by making you responsible for generating the
SQL dynamically, a move I regard as a horrible UX failure.

> > 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)".
> The SERIALIZATION looks interesting, but I believe these days JSON
> would make more sense than XML, both as easier for the client-side and
> because of all the json_* functions we now have to mix json with
> relational structures.

I proposed SERIALIZATION as a parameter precisely so we could use
different ones for different cases. JSON is certainly popular this
year, as XML was in prior years. I may be wrong, but I'm certain that
there will be new ones, even popular ones, that haven't yet been

David Fetter <david(at)fetter(dot)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:

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2015-08-11 16:55:08 Intentional usage of old style function declarations?
Previous Message Robert Haas 2015-08-11 16:53:08 Re: Moving SS_finalize_plan processing to the end of planning