Re: Question(s) about crosstab

Ken Tanzer wrote
> 1) Is there a simpler way? I'm hoping I made this unnecessarily
> cumbersome and complicated.
> 2) AFAICT, if a new unit type were to be added, I'd have to rewrite this
> query. Is there any way to avoid that?
> 3) It seems like everything after the first query, except for the
> category
> field, is redundant information, and that in theory you should be able to
> say crosstab('query','category_field'). Is there any inherent reason this
> simpler form couldn't work, or is it just that no one has wanted to do it,
> or gotten to it yet?

1) Pivot Tables...(not a PostgreSQL feature I'm afraid)

2) Not that I am aware of. I would suggest writing the query so that
"Other" is a valid group and any unmapped types get aliased to "Other" so at
least the query counts everything and you know that if "Other" is non-zero
you have some alterations to make.

3) Limitation of SQL - explained below:

The function call string that you pass in is just that, a string, the SQL
construct within which it resides has no knowledge of its contents.

SQL has the hard requirement that at the time you submit a query all columns
must be known. If a function is polymorphic (in the sense it can output
different columns/row-types) then when you call that function you must
indicate which columns (and types) are going to be output by the function
during this specific execution.

As an aside you should consider dollar-quoting:

SELECT function_call(
$arg1$ SELECT '1' AS one $arg1$, $arg2$ SELECT '2two' AS "22" $arg2"
);

That way you do not need to deal with escaping the embedded quotes in the
query and can fairly easily extract the query text and run it standalone,
modify it, then copy it back without modification.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Question-s-about-crosstab-tp5783810p5783818.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.