1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
|
CREATE FUNCTION fn_giteqbe(
id_dep integer,
name_ville character varying,
id_reg integer,
chambre_min integer,
chambre_max integer,
couchage_min integer,
couchage_max integer,
surface_min integer,
surface_max integer)
RETURNS TABLE(id_gite integer, nom_gite character varying, surface_habitable integer, nb_chambres integer, nb_couchages integer, id_personne integer, nom_personne character varying, numero_telephone character varying, email character varying, id_ville integer, code_insee character varying, nom_ville character varying, code_postal character varying, latitude double precision, longitude double precision, id_departement integer, nom_departement character varying, code_departement character varying, id_region integer, nom_region character varying, code_region character varying, id_proprietaire integer, nom_proprio character varying, numero_telephone_proprio character varying, email_proprio character varying, adresse character varying, adresse_2 character varying)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE clause varchar;
DECLARE dep varchar;
DECLARE ville varchar;
DECLARE region varchar;
DECLARE chambre varchar;
DECLARE couchage varchar;
DECLARE surface varchar;
BEGIN
IF id_dep IS NULL then
dep := 'WHERE 1=1 ';
else
dep := 'WHERE v.id_departement = ' || id_dep;
end if;
IF name_ville is null then
ville := ' ';
else
ville := ' AND v.nom_ville like ' || chr(39) || '%' || name_ville || '%' || chr(39);
end if;
IF id_reg is null then
region := ' ';
else
region := ' AND r.id_region = ' || id_reg ;
end if;
IF chambre_min is null then
chambre_min := 1;
end if;
IF chambre_max is null then
chambre_max := 15;
end if;
chambre := ' AND nb_chambres between ' || chambre_min || ' AND ' || chambre_max;
IF couchage_min is null then
couchage_min := 1;
end if;
IF couchage_max is null then
couchage_max := 50;
end if;
couchage := ' AND nb_couchages between ' || couchage_min || ' AND ' || couchage_max;
IF surface_min is null then
surface_min := 40;
end if;
IF surface_max is null then
surface_max := 300;
end if;
surface := ' AND surface_habitable between ' || surface_min || ' AND ' || surface_max;
clause := 'SELECT g.id_gite , g.nom_gite
, g.surface_habitable
, g.nb_chambres
, g.nb_couchages
, g.id_personne
, p1.nom_personne
, p1.numero_telephone
, p1.email
, g.id_ville
, v.code_insee
, v.nom_ville
, v.code_postal
, v.latitude
, v.longitude
, v.id_departement
, d.nom_departement
, d.code_departement
, d.id_region
, r.nom_region
, r.code_region
, g.id_proprietaire
, p2.nom_personne as nom_proprio
, p2.numero_telephone as numero_telephone_proprio
, p2.email as email_proprio
, g.adresse
, g.adresse_2
FROM gite as g join personne as p1
on g.id_personne = p1.id_personne
join personne as p2 on g.id_proprietaire = p2.id_personne
join ville as v on
g.id_ville = v.id_ville join departement as d
on v.id_departement = d.id_departement
join region as r on d.id_region = r.id_region ';
RETURN QUERY
SELECT * FROM (clause || dep || ville || region || chambre || couchage || surface);
END
$BODY$;
ALTER FUNCTION public.fn_giteqbe(integer, character varying, integer, integer, integer, integer, integer, integer, integer)
OWNER TO postgres; |
Partager