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