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$; |
