Skip site navigation (1) Skip section navigation (2)

Re: Pet Peeves?

From: Jason Long <jason(at)octgsoftware(dot)com>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: Daniel Verite <daniel(at)manitou-mail(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Re: Pet Peeves?
Date: 2009-01-30 14:46:10
Message-ID: 498312B2.3060007@supernovasoftware.com (view raw or flat)
Thread:
Lists: pgsql-general
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.
>
>   
>> 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.
>
>   
PIVOT would prove very valuable to my application. :)

In response to

pgsql-general by date

Next:From: A BDate: 2009-01-30 14:52:44
Subject: Runaway postgres process?
Previous:From: Gregory StarkDate: 2009-01-30 14:25:28
Subject: Re: Pet Peeves?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group