Re: How to put multiples results in just one column

2013/1/31 Nei Rauni Santos <nrauni(at)gmail(dot)com>:
> Thank you Pavel,
>
> I could do that like this:
>
>
> select p.id,
>
> ( select array_accum ((
> room_name, room_id, room_group_name, room_group_id, room_order,
> availability_min, price_amount, price_min, price_avg, price_balcony_amount,
> price_balcony_avg, capacity, deposit_required, breakfast_included,
> room_min_stay
> )::cms.room_availability_list_type)
> from cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-05',
> 'pt_BR', 1, '{1}')
> ) room
> from wr.prestadores p
> where p.id = 2;
>
> the only problem is that it takes about 2293 ms for just one result.
>
>
> Any idea about the more effective way to do that?

you have to check queries inside function and you have to find slow
query and try to solve it.

http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions

one note - in your function there is lot of repeated queries to table
cms.room_availability_list - if this table is not small, then a
function cannot be super fast. A art of writing stored procedures is
in minimizing reading from large tables.

Regards

Pavel

>
>
>
>
>
> On Thu, Jan 31, 2013 at 10:39 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> fce
>
>
>
>
>
> --
> []s!!
>
> Nei
>