Re: Pet Peeves?

"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.

> As for Oracle, it wasn't possible until recently but now 11g has the PIVOT
> clause:
> http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f
> eatures/11g-pivot.html

From this the result columns do need to be explicitly listed in advance unless
you're asking for the pivot to be into an xml blob which seems like a whole
different feature really.

> In contrast of these clauses, PG's contrib/tablefunc looks more limited and
> quite harder to use.

Incidentally, the work-around I've used in the past was to aggregate the rows
into an array instead of separate columns. Definitely not the same of course,
just a work-around.

I think PIVOT is enticing too. It'll be interesting to see what happens in the
standard with the divergence between MSSQL and Oracle.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!