Creating PostgreSQL Arrays Without A Quadratic Blowup

<i>A lot of languages handle this idiom more gracefully.</i><p>Actually they don&#x27;t. You can not resize a (static) array. What you really want is a dynamic array, an array list or whatever you like to call it. In this case you can double the size of the underlying array when you have to reallocate it and get the amortized runtime down but at the price of using up to twice the memory you need.<p>Maybe array_append is to blame because its pure existence somewhat implies that you are working with array lists and not with arrays.

<i>result := array_append(result, (&#x27;num=&gt;&#x27; || i)::HSTORE);</i><p>Yeah, I&#x27;ve never read a line of PLPGSQL before this, but I can tell by the assignment operator := that this is obviously creating a copy of the array every time you add an element to the array. This would also cause a quadratic blowup and&#x2F;or a memory blowup in any other language I&#x27;ve heard of, not just PLPGSQL. Anyone who has taken an introductory algorithms &amp; data structures class really should know better than to write code like this.

I think highly of Postgres, but this is disappointing. It&#x27;s well-known now how to implement functional array-like data structures with log-time concatenation. For a good summary see [0].<p>I think these techniques should be standard practice in programming language implementations.<p>[0] <a href="http://stackoverflow.com/questions/3271256/implement-an-immutable-deque-as-a-balanced-binary-tree/3274355#3274355" rel="nofollow">http:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;3271256&#x2F;implement-an-immu...</a>

Seems guilty of trying to do imperative programming in a relational database, then wondering why the performance is not good. Why use an array and not rows in a table?

this is classic OO programmer programming SQL