On 2/25/19 8:34 PM, Merlin Moncure wrote:
> No worries, sir! Apologies on the late reply. I've made some headway on
> this item. Waiting for postgres to implement the SQL standard pivoting
> (regardless if it implements the cases I need) is not an option for my
> personal case. I can't use the SQL approach either as it's very slow and
> imposing some scaling limits that need to work around in the short run.
>
> My strategy is to borrow [steal] from crosstab_hash and make a new
> version called repivot which takes an arleady pivoted data set and
> repivots it against an identified column. Most of the code can be
> shared with tablefunc so ideally this could live as an amendment to that
> extension. That might not happen though, so I'm going to package it as
> a separate extension (removing the majority of tablefunc that I don't
> need) and submit it to this group for consideration.
I can't promise there will be consensus to add to tablefunc, but I am
not opposed and will be happy to try to help you make that happen to the
extent I can find the spare cycles.
> If we punt, it'll end up as a private extension or live the life of an
> Orphan in pgxn. If there's some interest here, we can consider a new
> contrib extension (which I personally rate very unlikely) or recasting
> as an extra routine to tablefunc. Any way we slice it, huge thanks to
> Joe Conway&co for giving us such an awesome function to work with all
> these years (not to mention the strategic plr language). SRF crosstab()
> is still somewhat baroque, but it still fills a niche that nothing else
> implements.
>
> The interface I'm looking at is:
> SELECT repivot(
> query TEXT,
> static_attributes INT, /* number of static attributes that are
> unchanged around key; we need this in our usages */
> attribute_query TEXT); /* query that draws up the pivoted attribute
> list */
>
> The driving query is expected to return 0+ static attributes which are
> essentially simply pasted to the output. The next two columns are the
> key column and the pivoting column. So if you had three attributes,
> the input set would be:
>
> a1, a2, a3, k1, p, v1...vn
>
> Where the coordinates v and p would exchange. I need to get this done
> quickly and so am trying to avoid more abstracted designs (maybe multi
> part keys should be supported through...this is big limitation of
> crosstab albeit with some obnoxious work arounds).
Perhaps not enough coffee yet, but I am not sure I fully grok the design
here. A fully fleshed out example would be useful.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development