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 ( count(state_code) 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 invented. Cheers, David. -- David Fetter <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers