Andrew wrote:
> It's in the SQL99 standard. There's nothing forcing you to use them -
I 
> am a (possibly) old-fashioned data architect, so I never use them ;-)

> SQL99 actually allows you to use more or less arbitrary composite
types 
> as columns (although Pg currently doesn't) - many would argue that
this 
> violates first normal form. 
<snip>

I would (strongly) disagree with your last statement: I believe the
opposite to be true. I do agree that they are a violation of the first
normal form when used a mechanism for general data storage;  however the
intent here is for arrays to help get around sql's difficulty (largely
due to the lack of recursive queries in postgres, but also in a more
general way) in dealing with post-query related data.  

Arrays (as a result of a query) help to enhance relational use of the
database by indirectly allowing a more natural method of storage by
giving you more power to query the data.  The main problem is sql's
general treatment of results sets as two dimensional tables when in fact
they are just possible branch points in a 'n' dimensional space
(specially expressed by arrays in limited, but useful circumstances).

In other words, arrays for input = bad, arrays for output = not so bad.
When recursive queries become available, I'll probably use them
instead(I've never had the luxury), but in the mean time...

p.s.  
Joe, you were right I did misunderstand both you and postgres's
capabilities at the present time.  The functionality in your example was
exactly what I was looking for.  I still hold to my point that if the
array is performing deep copies upon growth, there can be vast speed
improvements in cases (such as during the array aggregation) when
aggressive growth can be predicted in advance.  The worst case of
'reallocate after each aggregation' can be particularly bad.  In any
case, I'll shut up now :)

Regards,
Merlin




Tue, 29 Jul 2003 16:10:50 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: an aggregate array function

well, (smile) I didn't say *I* saw violation of FNF as an objection. I 
think my statement is true - many would see it as a violation of FNF. 
Many others like you might argue differently.

I first got into this business nearly 20 years ago when I came to 
realise the severe limitations of the relational algebra. Nothing much 
has changed about that - it can still be very cumbersome, to say the 
least. So I'm not dogmatic about obeying some design law that some 
theorist has laid down. It's more like the English grammar rule about 
split infinitives - I understand why some people disagree with the rule 
(or deny it exists), but I still (almost) never use split infinitives 
myself - I just don't find the need.

*shrug* Never mind - I'll go back to wrestling with Ant now.

cheers

andrew


Merlin Moncure wrote:

>Andrew wrote:
>  
>
>>It's in the SQL99 standard. There's nothing forcing you to use them -
>>    
>>
>I 
>  
>
>>am a (possibly) old-fashioned data architect, so I never use them ;-)
>>    
>>
>
>  
>
>>SQL99 actually allows you to use more or less arbitrary composite
>>    
>>
>types 
>  
>
>>as columns (although Pg currently doesn't) - many would argue that
>>    
>>
>this 
>  
>
>>violates first normal form. 
>>    
>>
><snip>
>
>I would (strongly) disagree with your last statement: I believe the
>opposite to be true. I do agree that they are a violation of the first
>normal form when used a mechanism for general data storage;  however the
>intent here is for arrays to help get around sql's difficulty (largely
>due to the lack of recursive queries in postgres, but also in a more
>general way) in dealing with post-query related data.  
>
>Arrays (as a result of a query) help to enhance relational use of the
>database by indirectly allowing a more natural method of storage by
>giving you more power to query the data.  The main problem is sql's
>general treatment of results sets as two dimensional tables when in fact
>they are just possible branch points in a 'n' dimensional space
>(specially expressed by arrays in limited, but useful circumstances).
>
>In other words, arrays for input = bad, arrays for output = not so bad.
>When recursive queries become available, I'll probably use them
>instead(I've never had the luxury), but in the mean time...
>
>p.s.  
>Joe, you were right I did misunderstand both you and postgres's
>capabilities at the present time.  The functionality in your example was
>exactly what I was looking for.  I still hold to my point that if the
>array is performing deep copies upon growth, there can be vast speed
>improvements in cases (such as during the array aggregation) when
>aggressive growth can be predicted in advance.  The worst case of
>'reallocate after each aggregation' can be particularly bad.  In any
>case, I'll shut up now :)
>
>Regards,
>Merlin
>
>  
>



Tue, 29 Jul 2003 16:49:35 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: an aggregate array function