Re: PostgreSQL Function Language Performance: C vs PL/PGSQL

On Tue, Jun 1, 2010 at 8:59 AM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
> On Tue, 1 Jun 2010, Stephen Frost wrote:
>>
>> * Matthew Wakeling (matthew(at)flymine(dot)org) wrote:
>>>
>>> The major case I found when writing pl/pgsql was when trying to build
>>> arrays row by row. AFAIK when I tried it, adding a row to an array caused
>>> the whole array to be copied, which put a bit of a damper on performance.
>>
>> Using the built-ins now available in 8.4 (array_agg), that copying
>> doesn't happen any more.
>
> Thanks. I had wondered if that had been improved.

even better is array(query) -- which has been around for a while. not
too many people know about it because it's syntactically weird but
it's the preferred way to build arrays when you don't need true
aggregation (group by and such).

generally speaking, concatenation of any kind in loops should be
avoided in pl/pgsql. in fact, whenever writing pl/pgsql, it's all to
easy to over-use the loop construct...every time you're looping it's
always good to ask yourself: 'can this be done in a query?'.

merlin