Обсуждение: crosstab query script (python) attached

For those who may be interested, I have attached my
python script for creating single table crosstab
queries using PyGreSQL.

Once it gets cleaned up a little, I'd like to
contribute it to the TechDocs site.  There's probably
much more elegant ways of accomplishing the task on
the server side; but for non-admin users, it could be
helpful.  Also, it should be easy to translate the
method to other tools/languages.

Thanks,

Andrew Gould

Andrew Gould wrote:
> Once it gets cleaned up a little, I'd like to
> contribute it to the TechDocs site.  There's probably
> much more elegant ways of accomplishing the task on
> the server side; but for non-admin users, it could be
> helpful.  Also, it should be easy to translate the
> method to other tools/languages.
>

Just for info, there is a (C language) crosstab function in
contrib/tablefunc.

The one in 7.3 has a serious limitation (the source query must ensure
that for each row in the crosstab, there is a record representing each
column of the crosstab, even if the value is NULL).

However the version in 7.4devel (and a 7.3.x compatible update here
http://www.joeconway.com/) eliminates that limitation.

Joe

--- Joe Conway <mail@joeconway.com> wrote:
>
> Just for info, there is a (C language) crosstab
> function in
> contrib/tablefunc.
>
> The one in 7.3 has a serious limitation (the source
> query must ensure
> that for each row in the crosstab, there is a record
> representing each
> column of the crosstab, even if the value is NULL).

> Joe

I'm not sure that's a bad thing.  Crosstabs are a
great tool for analysis.  My first task is usually to
check for unexpected column titles and unusual volumes
in the "null" column.  My script creates a
"null_value" field to account for records without
values for the crosstab.  (Initially, I called it
"Null"; but I got nervous about using the word as a
column title.)

Andrew Gould