Thread: How to put multiples results in just one column

How to put multiples results in just one column

From
Nei Rauni Santos
Date:
Hi,

The problem is, I'm working in a list of hotels which should have availability of rooms and list the hotel and its rooms on the application.

I have this function which already is used to get the rooms available
select  cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-02', 'pt_BR', 1, '{1}')

which result is a list of rooms ( type ) for a specific hotel.

("Apartment single",2117,"Apartamento Superior",1681,4,10,100.00,100.00,100.00,127.32,127.32,3,1,{1},)"
"("Apartment single 2",4981,"Apartamento Superior",1681,6,10,100.00,100.00,100.00,149.80,149.80,5,1,{1},)"
"("Apartment double",13862,"Apartamento Luxo",4311,11,10,100.00,100.00,100.00,107.99,107.99,2,1,{1},)"
"("Suite double",13867,"Suíte Executiva",4313,15,10,100.00,100.00,100.00,174.32,174.32,1,1,{1},)

I need to get one result of hotel's table and a way to return all the rows available in a single column as a array with all the data showed below.

Is that possible?

thank you,


Follow my function responsable to filter rooms available:

CREATE OR REPLACE FUNCTION cms.sp_get_supplier_availability(in_supplier_id integer, in_checkin date, in_checkout date, in_culture character varying, in_room_qty integer, in_people_qty integer[])
  RETURNS SETOF cms.room_availability_list_type AS
$BODY$  DECLARE
    i INTEGER;
    AVAIL INTEGER[];
    DIFF_DAYS INTEGER;
    _room_availability cms.room_availability_list_type%rowtype;
    _room RECORD;
  BEGIN

    IF( supplier.available_for_booking( in_supplier_id ) IS FALSE ) THEN
        RETURN ;
    END IF;
   
    -- release
    IF(cms.sp_supplier_release(in_supplier_id, in_checkin) IS FALSE) THEN
      RETURN ;
    END IF;

    IF (cms.sp_supplier_is_unavailable(in_supplier_id, in_checkin, in_checkout)) THEN
      RETURN ;
    END IF;
   
   
    DIFF_DAYS := (IN_CHECKOUT - IN_CHECKIN);
    i:=0;
    AVAIL := NULL;

    IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
      SELECT array_accum(ra.room_id) INTO AVAIL FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_allow_check_in IS TRUE;
    ELSE
      IF IN_PEOPLE_QTY IS NULL AND IN_ROOM_QTY IS NOT NULL THEN
        SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_allow_check_in IS TRUE GROUP BY ra.day HAVING sum(ra.room_real_availability) >= IN_ROOM_QTY;
      ELSE
        SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_allow_check_in IS TRUE AND ra.room_id IN (SELECT DISTINCT room_avail.room_id FROM cms.sp_get_room_people_capacity(IN_SUPPLIER_ID, IN_ROOM_QTY, IN_PEOPLE_QTY, in_culture) room_avail) GROUP BY ra.day HAVING sum(ra.room_real_availability) >= IN_ROOM_QTY;
      END IF;
    END IF;

    IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN

      SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
      FROM cms.room_availability ra
      INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
      WHERE
    q.prestadores_id = IN_SUPPLIER_ID AND
    ra.day = IN_CHECKOUT AND
    ra.allow_check_out IS TRUE AND
    ra.room_id IN (SELECT explode_array(AVAIL) as data);

    ELSE

      IF IN_PEOPLE_QTY IS NULL THEN

        SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
        FROM cms.room_availability ra
        INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
        WHERE
          q.prestadores_id = IN_SUPPLIER_ID AND
          ra.day = IN_CHECKOUT AND
          ra.allow_check_out IS TRUE AND
          ra.room_id IN (SELECT explode_array(AVAIL) as data)
        GROUP BY ra.day;

      ELSE

        SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
        FROM cms.room_availability ra
        INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
        WHERE
          q.prestadores_id = IN_SUPPLIER_ID AND
          ra.day = IN_CHECKOUT AND
          ra.allow_check_out IS TRUE AND
          ra.room_id IN (SELECT explode_array(AVAIL) as data)
        GROUP BY ra.day;

      END IF;

    END IF;

    IF DIFF_DAYS > 1 THEN

      IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN

        SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT ra.room_id FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_id IN (SELECT explode_array(AVAIL) as data) GROUP BY ra.room_id HAVING count(ra.room_id) = (DIFF_DAYS-1)) r;
      ELSE
        IF IN_PEOPLE_QTY IS NULL THEN

          SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT ra.room_id, min(ra.room_real_availability) as room_real_availability FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_id IN (SELECT explode_array(AVAIL) as data) GROUP BY ra.room_id ) r HAVING sum(r.room_real_availability) >= IN_ROOM_QTY;
        ELSE
          --RAISE NOTICE 'busca quartos com disponibilidade para IN_PEOPLE_QTY is true';
          SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT ra.room_id, min(ra.room_real_availability) as room_real_availability FROM cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_id IN (SELECT explode_array(AVAIL) as data) AND ra.room_id IN (SELECT DISTINCT room_avail.room_id FROM cms.sp_get_room_people_capacity(IN_SUPPLIER_ID, IN_ROOM_QTY, IN_PEOPLE_QTY, in_culture) room_avail) GROUP BY ra.room_id ) r HAVING sum(r.room_real_availability) >= IN_ROOM_QTY;
        END IF;
      END IF;
    ELSE
      --RAISE NOTICE 'diff_days é igual a 1, nao faz nada';
    END IF;

    SELECT array_accum(room_id) INTO AVAIL FROM (
      SELECT DISTINCT ra.room_id
                 FROM cms.room_availability_list ra
                WHERE ra.room_id IN (SELECT explode_array(AVAIL) as data)
                  AND ra.day >= IN_CHECKIN
                  AND ra.day < IN_CHECKOUT
             GROUP BY ra.room_id
               HAVING max(ra.room_min_stay) <= DIFF_DAYS
    ) AS dados;

    IF AVAIL IS NULL THEN
      --RAISE NOTICE 'não encontrou disponibilidade, retorna vazio';
      return ;
    END IF;

    IF (SELECT array_int_len(AVAIL)) IS NOT NULL THEN

        --RAISE NOTICE 'ENTROU NO IF';
        FOR _room_availability IN SELECT ral.room_alias as room_name, ral.room_id as room_id, ral.room_group_name as room_group_name, ral.room_group_id as room_group_id, ral.room_order, min(room_real_availability) as availability_min, sum(ral.room_price) as price_amount, min(ral.room_price) as price_min, avg(ral.room_price) as price_min, sum(ral.room_balcony_price) as price_balcony_amount, avg(ral.room_balcony_price) as price_balcony_avg, ral.room_capacity as capacity, (SELECT DISTINCT(ral2.deposit_required) FROM cms.room_availability_list ral2 WHERE ral2.room_id = ral.room_id AND ral2.day = IN_CHECKIN AND ral2.culture = IN_CULTURE) as deposit_required, (SELECT array_accum( DISTINCT ral3.breakfast_included ) FROM cms.room_availability_list ral3 WHERE ral3.room_id = ral.room_id AND ral3.day >= IN_CHECKIN AND ral3.day < IN_CHECKOUT AND ral3.culture = IN_CULTURE) as breakfast_included FROM cms.room_availability_list ral WHERE ral.room_id IN (SELECT explode_array(AVAIL) as data) AND ral.day >= IN_CHECKIN AND ral.day < IN_CHECKOUT AND ral.culture = IN_CULTURE AND ral.room_price > 10 GROUP BY ral.room_id, ral.room_alias, ral.room_group_id, ral.room_group_name, ral.room_capacity, ral.room_order HAVING count(ral.room_id) = DIFF_DAYS ORDER BY ral.room_order, ral.room_alias LOOP

          RETURN NEXT _room_availability;

        END LOOP;

    END IF;

    return ;

  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cms.sp_get_supplier_availability(integer, date, date, character varying, integer, integer[])
  OWNER TO reserva;


CREATE TYPE cms.room_availability_list_type AS
   (room_name character varying,
    room_id integer,
    room_group_name character varying,
    room_group_id integer,
    room_order integer,
    availability_min smallint,
    price_amount numeric(10,2),
    price_min numeric(10,2),
    price_avg numeric(10,2),
    price_balcony_amount numeric(10,2),
    price_balcony_avg numeric(10,2),
    capacity smallint,
    deposit_required integer,
    breakfast_included integer[],
    room_min_stay smallint);
ALTER TYPE cms.room_availability_list_type
  OWNER TO reserva;


--
[]s!!

Nei

Re: How to put multiples results in just one column

From
Pavel Stehule
Date:
Hello

select (fce(..)).column from ...

or select column from fce()

Regards

Pavel Stehule

2013/1/31 Nei Rauni Santos <nrauni@gmail.com>:
> Hi,
>
> The problem is, I'm working in a list of hotels which should have
> availability of rooms and list the hotel and its rooms on the application.
>
> I have this function which already is used to get the rooms available
> select  cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-02',
> 'pt_BR', 1, '{1}')
>
> which result is a list of rooms ( type ) for a specific hotel.
>
> ("Apartment single",2117,"Apartamento
> Superior",1681,4,10,100.00,100.00,100.00,127.32,127.32,3,1,{1},)"
> "("Apartment single 2",4981,"Apartamento
> Superior",1681,6,10,100.00,100.00,100.00,149.80,149.80,5,1,{1},)"
> "("Apartment double",13862,"Apartamento
> Luxo",4311,11,10,100.00,100.00,100.00,107.99,107.99,2,1,{1},)"
> "("Suite double",13867,"Suíte
> Executiva",4313,15,10,100.00,100.00,100.00,174.32,174.32,1,1,{1},)
>
> I need to get one result of hotel's table and a way to return all the rows
> available in a single column as a array with all the data showed below.
>
> Is that possible?
>
> thank you,
>
>
> Follow my function responsable to filter rooms available:
>
> CREATE OR REPLACE FUNCTION cms.sp_get_supplier_availability(in_supplier_id
> integer, in_checkin date, in_checkout date, in_culture character varying,
> in_room_qty integer, in_people_qty integer[])
>   RETURNS SETOF cms.room_availability_list_type AS
> $BODY$  DECLARE
>     i INTEGER;
>     AVAIL INTEGER[];
>     DIFF_DAYS INTEGER;
>     _room_availability cms.room_availability_list_type%rowtype;
>     _room RECORD;
>   BEGIN
>
>     IF( supplier.available_for_booking( in_supplier_id ) IS FALSE ) THEN
>         RETURN ;
>     END IF;
>
>     -- release
>     IF(cms.sp_supplier_release(in_supplier_id, in_checkin) IS FALSE) THEN
>       RETURN ;
>     END IF;
>
>     IF (cms.sp_supplier_is_unavailable(in_supplier_id, in_checkin,
> in_checkout)) THEN
>       RETURN ;
>     END IF;
>
>
>     DIFF_DAYS := (IN_CHECKOUT - IN_CHECKIN);
>     i:=0;
>     AVAIL := NULL;
>
>     IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
>       SELECT array_accum(ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE;
>     ELSE
>       IF IN_PEOPLE_QTY IS NULL AND IN_ROOM_QTY IS NOT NULL THEN
>         SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE GROUP BY ra.day HAVING
> sum(ra.room_real_availability) >= IN_ROOM_QTY;
>       ELSE
>         SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day = IN_CHECKIN AND ra.room_real_availability > 0 AND ra.room_price >=
> 10 AND ra.room_allow_check_in IS TRUE AND ra.room_id IN (SELECT DISTINCT
> room_avail.room_id FROM cms.sp_get_room_people_capacity(IN_SUPPLIER_ID,
> IN_ROOM_QTY, IN_PEOPLE_QTY, in_culture) room_avail) GROUP BY ra.day HAVING
> sum(ra.room_real_availability) >= IN_ROOM_QTY;
>       END IF;
>     END IF;
>
>     IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
>
>       SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
>       FROM cms.room_availability ra
>       INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
>       WHERE
>     q.prestadores_id = IN_SUPPLIER_ID AND
>     ra.day = IN_CHECKOUT AND
>     ra.allow_check_out IS TRUE AND
>     ra.room_id IN (SELECT explode_array(AVAIL) as data);
>
>     ELSE
>
>       IF IN_PEOPLE_QTY IS NULL THEN
>
>         SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
>         FROM cms.room_availability ra
>         INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
>         WHERE
>           q.prestadores_id = IN_SUPPLIER_ID AND
>           ra.day = IN_CHECKOUT AND
>           ra.allow_check_out IS TRUE AND
>           ra.room_id IN (SELECT explode_array(AVAIL) as data)
>         GROUP BY ra.day;
>
>       ELSE
>
>         SELECT array_accum( DISTINCT ra.room_id) INTO AVAIL
>         FROM cms.room_availability ra
>         INNER JOIN cms.quartos q ON ( ra.room_id = q.id )
>         WHERE
>           q.prestadores_id = IN_SUPPLIER_ID AND
>           ra.day = IN_CHECKOUT AND
>           ra.allow_check_out IS TRUE AND
>           ra.room_id IN (SELECT explode_array(AVAIL) as data)
>         GROUP BY ra.day;
>
>       END IF;
>
>     END IF;
>
>     IF DIFF_DAYS > 1 THEN
>
>       IF IN_ROOM_QTY IS NULL AND IN_PEOPLE_QTY IS NULL THEN
>
>         SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT
> ra.room_id FROM cms.room_availability_list ra WHERE ra.supplier_id =
> IN_SUPPLIER_ID AND ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND
> ra.room_real_availability > 0 AND ra.room_price >= 10 AND ra.room_id IN
> (SELECT explode_array(AVAIL) as data) GROUP BY ra.room_id HAVING
> count(ra.room_id) = (DIFF_DAYS-1)) r;
>       ELSE
>         IF IN_PEOPLE_QTY IS NULL THEN
>
>           SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT
> ra.room_id, min(ra.room_real_availability) as room_real_availability FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND ra.room_real_availability >
> 0 AND ra.room_price >= 10 AND ra.room_id IN (SELECT explode_array(AVAIL) as
> data) GROUP BY ra.room_id ) r HAVING sum(r.room_real_availability) >=
> IN_ROOM_QTY;
>         ELSE
>           --RAISE NOTICE 'busca quartos com disponibilidade para
> IN_PEOPLE_QTY is true';
>           SELECT array_accum( DISTINCT r.room_id) INTO AVAIL FROM (SELECT
> ra.room_id, min(ra.room_real_availability) as room_real_availability FROM
> cms.room_availability_list ra WHERE ra.supplier_id = IN_SUPPLIER_ID AND
> ra.day > IN_CHECKIN AND ra.day < IN_CHECKOUT AND ra.room_real_availability >
> 0 AND ra.room_price >= 10 AND ra.room_id IN (SELECT explode_array(AVAIL) as
> data) AND ra.room_id IN (SELECT DISTINCT room_avail.room_id FROM
> cms.sp_get_room_people_capacity(IN_SUPPLIER_ID, IN_ROOM_QTY, IN_PEOPLE_QTY,
> in_culture) room_avail) GROUP BY ra.room_id ) r HAVING
> sum(r.room_real_availability) >= IN_ROOM_QTY;
>         END IF;
>       END IF;
>     ELSE
>       --RAISE NOTICE 'diff_days é igual a 1, nao faz nada';
>     END IF;
>
>     SELECT array_accum(room_id) INTO AVAIL FROM (
>       SELECT DISTINCT ra.room_id
>                  FROM cms.room_availability_list ra
>                 WHERE ra.room_id IN (SELECT explode_array(AVAIL) as data)
>                   AND ra.day >= IN_CHECKIN
>                   AND ra.day < IN_CHECKOUT
>              GROUP BY ra.room_id
>                HAVING max(ra.room_min_stay) <= DIFF_DAYS
>     ) AS dados;
>
>     IF AVAIL IS NULL THEN
>       --RAISE NOTICE 'não encontrou disponibilidade, retorna vazio';
>       return ;
>     END IF;
>
>     IF (SELECT array_int_len(AVAIL)) IS NOT NULL THEN
>
>         --RAISE NOTICE 'ENTROU NO IF';
>         FOR _room_availability IN SELECT ral.room_alias as room_name,
> ral.room_id as room_id, ral.room_group_name as room_group_name,
> ral.room_group_id as room_group_id, ral.room_order,
> min(room_real_availability) as availability_min, sum(ral.room_price) as
> price_amount, min(ral.room_price) as price_min, avg(ral.room_price) as
> price_min, sum(ral.room_balcony_price) as price_balcony_amount,
> avg(ral.room_balcony_price) as price_balcony_avg, ral.room_capacity as
> capacity, (SELECT DISTINCT(ral2.deposit_required) FROM
> cms.room_availability_list ral2 WHERE ral2.room_id = ral.room_id AND
> ral2.day = IN_CHECKIN AND ral2.culture = IN_CULTURE) as deposit_required,
> (SELECT array_accum( DISTINCT ral3.breakfast_included ) FROM
> cms.room_availability_list ral3 WHERE ral3.room_id = ral.room_id AND
> ral3.day >= IN_CHECKIN AND ral3.day < IN_CHECKOUT AND ral3.culture =
> IN_CULTURE) as breakfast_included FROM cms.room_availability_list ral WHERE
> ral.room_id IN (SELECT explode_array(AVAIL) as data) AND ral.day >=
> IN_CHECKIN AND ral.day < IN_CHECKOUT AND ral.culture = IN_CULTURE AND
> ral.room_price > 10 GROUP BY ral.room_id, ral.room_alias, ral.room_group_id,
> ral.room_group_name, ral.room_capacity, ral.room_order HAVING
> count(ral.room_id) = DIFF_DAYS ORDER BY ral.room_order, ral.room_alias LOOP
>
>           RETURN NEXT _room_availability;
>
>         END LOOP;
>
>     END IF;
>
>     return ;
>
>   END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100
>   ROWS 1000;
> ALTER FUNCTION cms.sp_get_supplier_availability(integer, date, date,
> character varying, integer, integer[])
>   OWNER TO reserva;
>
>
> CREATE TYPE cms.room_availability_list_type AS
>    (room_name character varying,
>     room_id integer,
>     room_group_name character varying,
>     room_group_id integer,
>     room_order integer,
>     availability_min smallint,
>     price_amount numeric(10,2),
>     price_min numeric(10,2),
>     price_avg numeric(10,2),
>     price_balcony_amount numeric(10,2),
>     price_balcony_avg numeric(10,2),
>     capacity smallint,
>     deposit_required integer,
>     breakfast_included integer[],
>     room_min_stay smallint);
> ALTER TYPE cms.room_availability_list_type
>   OWNER TO reserva;
>
>
> --
> []s!!
>
> Nei
>



Re: How to put multiples results in just one column

From
Nei Rauni Santos
Date:
<div dir="ltr"><div class="gmail_extra">Thank you Pavel,<br /><br /></div><div class="gmail_extra">I could do that like
this:<br/><br /><br />select <a href="http://p.id">p.id</a>, <br /><br />( select array_accum ((<br />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<br />
)::cms.room_availability_list_type)<br />from cms.sp_get_supplier_availability(2, '2013-02-01', '2013-02-05', 'pt_BR',
1,'{1}')<br />) room <br />from wr.prestadores p <br />where <a href="http://p.id">p.id</a> = 2;<br /><br />the only
problemis that it takes about 2293 ms for just one result.<br /><br /><br /></div><div class="gmail_extra">Any idea
aboutthe more effective way to do that?<br /></div><div class="gmail_extra"><br /><br /></div><div
class="gmail_extra"><br/></div><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Thu, Jan 31, 2013 at
10:39AM, Pavel Stehule <span dir="ltr"><<a href="mailto:pavel.stehule@gmail.com"
target="_blank">pavel.stehule@gmail.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0px 0px
0px0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">fce</blockquote></div><br /><br /><br clear="all"
/><br/>-- <br />[]s!!<br /><br />Nei<br /><br /></div></div> 

Re: How to put multiples results in just one column

From
Pavel Stehule
Date:
2013/1/31 Nei Rauni Santos <nrauni@gmail.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.stehule@gmail.com>
> wrote:
>>
>> fce
>
>
>
>
>
> --
> []s!!
>
> Nei
>