On 12/18/2013 03:32 PM, John Abraham wrote:
Regarding crosstab, yes it's basically too complicated to use directly. Here are the options: 1) write code (in another language, perhaps) to create your cross tab queries by inspecting the tables, which then submits those queries to create views. We have a web-app in django/python that will create crosstab views in this way. (We use it to attach the values to spatial shapes in PostGIS, so that other GIS programs, such as mapserver, can use it. GIS programs always seem to expect things to be in crosstab format.)
1) This is the best option. I've done it with plpgsql in the past. You don't need to inspect your tables so much as determine how many result columns to expect based on the categories SQL string. Once you know how many categories there are, you can define the column definition list which allows you to write the crosstab query. So basically your app calls the plpgsql function and then executes the resulting returned query string.
3) Can't "someone" write a pl language routine that does it better? I'd be willing to work on the core functionality in python if someone else would be willing to embed it in plpython (I've never used plpython.)
3) Not possible -- reason was given down thread. Column definition must be known/determinable by the parser prior to query execution.
Basically to improve this you would have to hack the postgres backend in such a way that it didn't need the column definition list until query execution time, which I also doubt is possible.
Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
