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