On Wed, May 25, 2011 at 9:17 PM, bubba postgres <bubba.postg...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
