Re: Pet Peeves?

On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote:
> Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org> writes:
>
> > A crosstab is not but a presentational transform of the data set. Any
> > information you would eventually need can be taken from the original
> > data source, one way or another. That's why dynamic-column crosstab are
> > not a problem, and the DBMS should not worry about providing the
> > information about the columns, maybe by simply not allowing the
> > dynamic-column ones in subqueries.
>
> What about a WHERE clause like
>
> WHERE P1 > P2

You could either:

(1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2",
generating the record set before applying the crosstab transformation.

(2) Since you are implying the existence of P1 and P2, use the
fixed-number-of-columns crosstab case instead, for which PG would
actually be able to give the column definitions without running the
query, and after that, joining the results with some other query.