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
| CREATE OR REPLACE PROCEDURE public.sp_giteqbe(
integer,
character varying,
integer,
integer,
integer,
integer,
integer,
integer,
integer)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE premiere varchar DEFAULT 'SELECT id_gite , nom_gite, surface_habitable, nb_chambres, ' ||
' 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, ' ||
' p2.numero_telephone, p2.email, adresse, 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 ';
DECLARE dep varchar;
DECLARE ville varchar;
DECLARE region varchar;
DECLARE chambre varchar;
DECLARE couchage varchar;
DECLARE surface varchar;
BEGIN
IF $1 IS NULL then
dep := 'WHERE 1=1 ';
else
dep := 'WHERE v.id_departement = ' || $1;
end if;
IF $2 is null then
ville := ' ';
else
ville := ' AND v.nom_ville like ' || chr(39) || '%' || $2 || '%' || chr(39);
end if;
IF $3 is null then
region := ' ';
else
region := ' AND r.id_region = ' || $3 ;
end if;
IF $4 is null then
$4 := 1;
end if;
IF $5 is null then
$5 := 15;
end if;
chambre := ' AND nb_chambres between ' || $4 || ' AND ' || $5;
IF $6 is null then
$6 := 1;
end if;
IF $7 is null then
$7 := 50;
end if;
couchage := ' AND nb_couchages between ' || $6 || ' AND ' || $7;
IF $8 is null then
$8 := 40;
end if;
IF $9 is null then
$9 := 300;
end if;
surface := ' AND surface_habitable between ' || $8 || ' AND ' || $9;
EXECUTE premiere || dep || ville || region || chambre || couchage || surface;
END;
$BODY$; |
Partager