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