Re: Pet Peeves?

On Fri, 2009-01-30 at 14:25 +0000, Gregory Stark wrote:
> "Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
>
> > Gregory Stark wrote:
> >
> >> Is it the hierarchical query ability you're looking for or pivot?
> >> The former we are actually getting in 8.4.
> >>
> >> AFAIK even in systems with pivot you still have to
> >> declare a fixed list of columns in advance anyways.
> >> Do you see a system where it works differently?
> >
> > MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without
> > the need to know in advance the number of columns:
> > http://msdn.microsoft.com/en-us/library/bb208956.aspx
>
> That's puzzling. I wonder what they do about clients requesting info about the
> results. Or for that matter such queries being used in subqueries or anywhere
> else where the surrounding code needs to know the type of results to expect.

It doesn't really matter. Since crosstabs are just a presentational
variation to a query with aggregate functions and GROUP BY clauses,
whenever you need the results in a crosstabbed recordset for further
processing you will either (a) know in advanced the final number of
columns --as in "I want the count for this, this and this attribute: 3
columns + row header"--, or (b) get the information from the original
aggregated subquery --as in "I want the longest path to traverse the
tree/graph"--.

So in fact, PG would be perfectly OK in not giving the information,
since it is not needed. However, it may be counterintuitive, so it
should be perfectly well documented.

Also, even if PG would manage to provide the precise result
characteristics in advance by evaluating the whole crosstab, the
information would not be trustworthy, since it may well change in the
next second.

I understand that this, being a presentational issue, might get me some
"this is not a DBMS issue"-like kind of responses, but (a) I definitely
trust PG speed and reliability more than PHP/Java/whatever language and
(b) I prefer to put all the hard work on the DB (or I would end up doing
JOINs myself).