I just finished a python script that performs a
crosstab query in PostgreSQL. Since I do most of my
analysis using MS Access, this was more of an academic
excercise than anything else.
I wrote the script externally to PostgreSQL using
PyGreSQL because:
1. The script can be copied and saved as different
queries; and
2. I'm am, admittedly, squeamish about new languages
and compiling additional features into any
application. (There is no 'IS' in 'CPA').
The script has the following features/limitations:
1. The user defines all database connection and query
variables near the top of the file.
2. The script builds the sql query from defined
variables.
3. SELECT INTO is used to store the result set in a
table. There is a variable to make the table
temporary.
4. The script does not allow for joining tables.
5. You can only use values from one column for
crosstab column headers.
6. I haven't learned about handling errors yet.
Limitations 4 and 5 can be worked around, I've done
both in MS Access; but the opportunity for errors was
too great for the first run.
As for #6, I wasn't interested in making errors! ;-)
(Yes, I know....I'll add error handling to my
'learning' list.)
If anyone is interested in the script, let me know.
It's under 3K in size.
Andrew Gould