Re: How to put multiples results in just one column

Hello

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

or select column from fce()

Regards

Pavel Stehule

2013/1/31 Nei Rauni Santos <nrauni(at)gmail(dot)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
>