On Wed, May 25, 2011 at 9:17 PM, bubba postgres
<bubba.postgres@gmail.com> wrote:
>
> So, what are the gotcha's around manipulating Arrays in stored procs?
> It seems reasonable that an array_cat /etc would cause the creation of a new
> array, but does mutating an existing array also create a copy?
Never, ever, if at all possible, build arrays with array_cat, ||
operator, etc. Try not to work with arrays iteratively. It will be
very slow. You have better options:
1. subquery array constructor:
array_var := array(select bar from foo where ...);
2. array_agg()
select into array_var array_agg(bar) from foo where ...
3. values array constructor:
array_var := array[1, 2, 3];
don't forget, in recent postgres, you can make arrays of composite
types as well, and also nest:
complex_type := array(select row(bar, array(select baz from bat where
..)) from foo);
For expanding arrays prefer unnest() and check out the coming 9.1
foreach feature
(http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY):
merlin
