On Fri, 2009-01-30 at 14:25 +0000, Gregory Stark wrote: > "Daniel Verite" <daniel@xxxxxxxxxxxxxxxx> 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). -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
