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