Conversion d'une requête en fonction

Version imprimable

Bonjour à tous,

Nouveau jour, nouvelle question.

Sur un projet j'ai la requête suivante :

Code:

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
select c.ident as ident,
	com.commune as commune,
	com.postal as postal,
	com.insee as insee,
	pm.zapm as pm,
	ch1.ident as ch_boite1,
	b1.ident as nom_boite1,
	ch2.ident as ch_boite2,
	b2.ident as nom_boite2, 
	c.finbt as capacite,
	c.longueur as longueur,
	c.dsig as date_dsig,
	s.nb_supp as nb_supports,
	c.firol_libe as role_fibre,
	ch.id_releve as releve_lie,
	nc.id_conception as dos_conception_lie,
	cc.fci as num_fci,
	co.etat_webop as etat_ca,
	co.date_valid_c9 as date_c9
from "siea_s_fibrvue.v_ca_assemble_cable" c
left join liste_communes com on right(c.insee,5) = com.insee
left join "siea_s_fibrza.geo_zapm_mcr" pm on st_intersects(c.geom,pm.geom)
left join "siea_s_fibrvue.v_gc_assemble_chambre" ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
left join "siea_s_fibrvue.v_gc_assemble_chambre" ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
left join "siea_s_fibrvue.v_ca_assemble_boitier" b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
left join "siea_s_fibrvue.v_ca_assemble_boitier" b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
left join (SELECT c.ident, COUNT(s.ident) as nb_supp
		from "siea_s_fibrvue.v_ca_assemble_cable" c
		left join "siea_s_fibrvue.v_gc_assemble_support" s on st_dwithin(c.geom,s.geom,0.2)
		group by c.ident) s on s.ident = c.ident
left join suivi_non_conformites nc on nc.cable = c.ident
left join suivi_chambres ch on nc.siea = ch.id_siea
left join suivi_cables_commandes cc on cc.id_cable = c.ident
left join suivi_commandes co on co.fci = cc.fci

Comme vous pouvez le voir elle n'a rien de très compliquée, je récupère juste tous les cables d'une table et ensuite je vais piocher dans plusieurs tables des infos complémentaires.

Cette requête est exploitée par un fichier excel donc codé en vba. Et pour faire un truc plus propre, éviter les doublons, etc, je voulais tranformer cette requête en une fonction intégrée directement à la base.
Pour cela j'ai utilisé la fonction create suivante :

Code:

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
create type cable as (ident character varying(40),
				commune character varying(254),
				postal character varying(254),
				insee character varying(254),
				pm character varying(80),
				ch_boite1 character varying(40),
				nom_boite1 character varying(40),
				ch_boite2 character varying(40),
				nom_boite2 character varying(40), 
				capacite numeric(10,0),
				longueur numeric,
				date_dsig character varying(80),
				nb_supports numeric,
				role_fibre character varying(254),
				releve_lie character varying(254),
				dos_conception_lie character varying(254),
				num_fci character varying(254),
				etat_ca character varying(254),
				date_c9 date);
 
 
CREATE OR REPLACE FUNCTION public.Get_Cables() RETURNS SETOF cable AS 
$BODY$
	DECLARE
 
	BEGIN
			select c.ident as ident,
				com.commune as commune,
				com.postal as postal,
				com.insee as insee,
				pm.zapm as pm,
				ch1.ident as ch_boite1,
				b1.ident as nom_boite1,
				ch2.ident as ch_boite2,
				b2.ident as nom_boite2, 
				c.finbt as capacite,
				c.longueur as longueur,
				c.dsig as date_dsig,
				s.nb_supp as nb_supports,
				c.firol_libe as role_fibre,
				ch.id_releve as releve_lie,
				nc.id_conception as dos_conception_lie,
				cc.fci as num_fci,
				co.etat_webop as etat_ca,
				co.date_valid_c9 as date_c9
			from "siea_s_fibrvue.v_ca_assemble_cable" c
			left join liste_communes com on right(c.insee,5) = com.insee
			left join "siea_s_fibrza.geo_zapm_mcr" pm on st_intersects(c.geom,pm.geom)
			left join "siea_s_fibrvue.v_gc_assemble_chambre" ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
			left join "siea_s_fibrvue.v_gc_assemble_chambre" ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
			left join "siea_s_fibrvue.v_ca_assemble_boitier" b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
			left join "siea_s_fibrvue.v_ca_assemble_boitier" b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
			left join (SELECT c.ident, COUNT(s.ident) as nb_supp
					from "siea_s_fibrvue.v_ca_assemble_cable" c
					left join "siea_s_fibrvue.v_gc_assemble_support" s on st_dwithin(c.geom,s.geom,0.2)
					group by c.ident) s on s.ident = c.ident
			left join suivi_non_conformites nc on nc.cable = c.ident
			left join suivi_chambres ch on nc.siea = ch.id_siea
			left join suivi_cables_commandes cc on cc.id_cable = c.ident
			left join suivi_commandes co on co.fci = cc.fci;
		RETURN;
	END;
	$BODY$
  LANGUAGE plpgsql;
ALTER FUNCTION public.Get_Cables()
  OWNER TO postgres;

Toute la création se passe bien mais une fois que j'exécute la fonction avec le code suivant :

Code:

select * from get_cables(NULL);

J'ai les erreurs suivantes :

Citation:

ERREUR: la requête n'a pas de destination pour les données résultantes
HINT: Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM à la place.
CONTEXT: fonction PL/pgsql get_cables(character varying), ligne 8 à instruction SQL
********** Erreur **********

ERREUR: la requête n'a pas de destination pour les données résultantes
État SQL :42601
Astuce : Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM à la place.
Contexte : fonction PL/pgsql get_cables(character varying), ligne 8 à instruction SQL

Et j'arrive pas à comprendre pourquoi.

Donc déjà pensez-vous que l'idée de mettre cette requête dans une fonction est une bonne idée ?
Et si oui savez-vous me dire si je suis obligé de passé par un type ceble comme je l'ai défini au départ ?
Et enfin sauriez-vous pourquoi j'ai cette erreur ?

PostGreSQL ne sait pas faire de véritables fonction table comme SQL Server. Il faut passer par une variable table interne....

Quelque chose comme :

Code:

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
CREATE OR REPLACE FUNCTION public.Get_Cables() 
RETURNS table
(           ident character varying(40),
				commune character varying(254),
				postal character varying(254),
				insee character varying(254),
				pm character varying(80),
				ch_boite1 character varying(40),
				nom_boite1 character varying(40),
				ch_boite2 character varying(40),
				nom_boite2 character varying(40), 
				capacite numeric(10,0),
				longueur numeric,
				date_dsig character varying(80),
				nb_supports numeric,
				role_fibre character varying(254),
				releve_lie character varying(254),
				dos_conception_lie character varying(254),
				num_fci character varying(254),
				etat_ca character varying(254),
				date_c9 date)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY
			select c.ident as ident,
				com.commune as commune,
				com.postal as postal,
				com.insee as insee,
				pm.zapm as pm,
				ch1.ident as ch_boite1,
				b1.ident as nom_boite1,
				ch2.ident as ch_boite2,
				b2.ident as nom_boite2, 
				c.finbt as capacite,
				c.longueur as longueur,
				c.dsig as date_dsig,
				s.nb_supp as nb_supports,
				c.firol_libe as role_fibre,
				ch.id_releve as releve_lie,
				nc.id_conception as dos_conception_lie,
				cc.fci as num_fci,
				co.etat_webop as etat_ca,
				co.date_valid_c9 as date_c9
			from "siea_s_fibrvue.v_ca_assemble_cable" c
			left join liste_communes com on right(c.insee,5) = com.insee
			left join "siea_s_fibrza.geo_zapm_mcr" pm on st_intersects(c.geom,pm.geom)
			left join "siea_s_fibrvue.v_gc_assemble_chambre" ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
			left join "siea_s_fibrvue.v_gc_assemble_chambre" ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
			left join "siea_s_fibrvue.v_ca_assemble_boitier" b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
			left join "siea_s_fibrvue.v_ca_assemble_boitier" b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
			left join (SELECT c.ident, COUNT(s.ident) as nb_supp
					from "siea_s_fibrvue.v_ca_assemble_cable" c
					left join "siea_s_fibrvue.v_gc_assemble_support" s on st_dwithin(c.geom,s.geom,0.2)
					group by c.ident) s on s.ident = c.ident
			left join suivi_non_conformites nc on nc.cable = c.ident
			left join suivi_chambres ch on nc.siea = ch.id_siea
			left join suivi_cables_commandes cc on cc.id_cable = c.ident
			left join suivi_commandes co on co.fci = cc.fci;
END;$$

C'est pas du tout performant !!!!

A +

Super ça fonctionne parfaitement, merci beaucoup.

Mais du coup je me retrouve avec 2 nouvelles questions :
- Pourquoi dites vous que ce n'est pas performant du tout ?
De mon coté j'ai 3 solutions pour mon outil :
- Soit je le fais sur Excel et là j'ai aussi 2 solutions soit je copie colle directement ma requête dans un string, c'est un peu lourd mais ca marche. Soit je passe par cette fonction que j'appelle directement dans le Excel et c'est un peu moins lourd.
- Soit je le fais sur Access et là pareil soit je met direct ma requête mais j'ai plein de problème de syntaxe avec que j'ai du mal a résoudre, soit j'appelle cette fonction et ça sera plus simple niveau syntaxe.

Vous en pensez quoi ?

Ensuite, question de gros débutant, si je voulais ajouter un if dans ma question je ferais comment ? J'ai tenter de faire ça :

Code:

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
CREATE OR REPLACE FUNCTION public.Get_Cables(id_cable character varying) 
RETURNS table
(       ident character varying(40),
	commune character varying(254),
	postal character varying(254),
	insee character varying(254),
	pm character varying(80),
	ch_boite1 character varying(40),
	nom_boite1 character varying(40),
	ch_boite2 character varying(40),
	nom_boite2 character varying(40), 
	capacite numeric(10,0),
	longueur numeric,
	date_dsig character varying(80),
	nb_supports bigint,
	role_fibre character varying(254),
	releve_lie character varying(254),
	dos_conception_lie character varying(254),
	num_fci character varying(254),
	etat_ca character varying(254),
	date_c9 date)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY
	-- only process parameter args once
	IF id_cable IS NULL THEN
		select c.ident as ident,
			com.commune as commune,
			com.postal as postal,
			com.insee as insee,
			pm.zapm as pm,
			ch1.ident as ch_boite1,
			b1.ident as nom_boite1,
			ch2.ident as ch_boite2,
			b2.ident as nom_boite2, 
			c.finbt as capacite,
			c.longueur as longueur,
			c.dsig as date_dsig,
			s.nb_supp as nb_supports,
			c.firol_libe as role_fibre,
			ch.id_releve as releve_lie,
			nc.id_conception as dos_conception_lie,
			cc.fci as num_fci,
			co.etat_webop as etat_ca,
			co.date_valid_c9 as date_c9
		from siea_s_fibrvue.v_ca_assemble_cable c
		left join liste_communes com on right(c.insee,5) = com.insee
		left join siea_s_fibrza_geo_zapm_mcr pm on st_intersects(c.geom,pm.geom)
		left join siea_s_fibrvue_v_gc_assemble_chambre ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
		left join siea_s_fibrvue_v_gc_assemble_chambre ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
		left join siea_s_fibrvue_v_ca_assemble_boitier b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
		left join siea_s_fibrvue_v_ca_assemble_boitier b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
		left join (SELECT c.ident, COUNT(s.ident) as nb_supp
				from siea_s_fibrvue_v_ca_assemble_cable c
				left join siea_s_fibrvue_v_gc_assemble_support s on st_dwithin(c.geom,s.geom,0.2)
				group by c.ident) s on s.ident = c.ident
		left join suivi_non_conformites nc on nc.cable = c.ident
		left join suivi_chambres ch on nc.siea = ch.id_siea
		left join suivi_cables_commandes cc on cc.id_cable = c.ident
		left join suivi_commandes co on co.fci = cc.fci;
	ELSE
		select c.ident as ident,
			com.commune as commune,
			com.postal as postal,
			com.insee as insee,
			pm.zapm as pm,
			ch1.ident as ch_boite1,
			b1.ident as nom_boite1,
			ch2.ident as ch_boite2,
			b2.ident as nom_boite2, 
			c.finbt as capacite,
			c.longueur as longueur,
			c.dsig as date_dsig,
			s.nb_supp as nb_supports,
			c.firol_libe as role_fibre,
			ch.id_releve as releve_lie,
			nc.id_conception as dos_conception_lie,
			cc.fci as num_fci,
			co.etat_webop as etat_ca,
			co.date_valid_c9 as date_c9
		from siea_s_fibrvue.v_ca_assemble_cable c
		left join liste_communes com on right(c.insee,5) = com.insee
		left join siea_s_fibrza_geo_zapm_mcr pm on st_intersects(c.geom,pm.geom)
		left join siea_s_fibrvue_v_gc_assemble_chambre ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
		left join siea_s_fibrvue_v_gc_assemble_chambre ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
		left join siea_s_fibrvue_v_ca_assemble_boitier b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
		left join siea_s_fibrvue_v_ca_assemble_boitier b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
		left join (SELECT c.ident, COUNT(s.ident) as nb_supp
				from siea_s_fibrvue_v_ca_assemble_cable c
				left join siea_s_fibrvue_v_gc_assemble_support s on st_dwithin(c.geom,s.geom,0.2)
				group by c.ident) s on s.ident = c.ident
		left join suivi_non_conformites nc on nc.cable = c.ident
		left join suivi_chambres ch on nc.siea = ch.id_siea
		left join suivi_cables_commandes cc on cc.id_cable = c.ident
		left join suivi_commandes co on co.fci = cc.fci
		where c.ident = id_cable;
	END IF;
END;$$

Mais je me retrouve avec l'erreur suivante :

Citation:

ERREUR: erreur de syntaxe sur ou près de « IF »
LINE 27: IF id_cable IS NULL THEN
^
********** Erreur **********

ERREUR: erreur de syntaxe sur ou près de « IF »
État SQL :42601
Caractère : 767

J'ai beau cherché sur le net je vois pas ou est le problème... :(

Salut
return query ne renvoie que du SELECT. Donc pas du procédural!
Essaye...

Code:

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
CREATE OR REPLACE FUNCTION public.Get_Cables(id_cable character varying) 
RETURNS table
(       ident character varying(40),
	commune character varying(254),
	postal character varying(254),
	insee character varying(254),
	pm character varying(80),
	ch_boite1 character varying(40),
	nom_boite1 character varying(40),
	ch_boite2 character varying(40),
	nom_boite2 character varying(40), 
	capacite numeric(10,0),
	longueur numeric,
	date_dsig character varying(80),
	nb_supports bigint,
	role_fibre character varying(254),
	releve_lie character varying(254),
	dos_conception_lie character varying(254),
	num_fci character varying(254),
	etat_ca character varying(254),
	date_c9 date)
LANGUAGE sql
AS $$
select c.ident as ident,
		com.commune as commune,
		com.postal as postal,
		com.insee as insee,
		pm.zapm as pm,
		ch1.ident as ch_boite1,
		b1.ident as nom_boite1,
		ch2.ident as ch_boite2,
		b2.ident as nom_boite2, 
		c.finbt as capacite,
		c.longueur as longueur,
		c.dsig as date_dsig,
		s.nb_supp as nb_supports,
		c.firol_libe as role_fibre,
		ch.id_releve as releve_lie,
		nc.id_conception as dos_conception_lie,
		cc.fci as num_fci,
		co.etat_webop as etat_ca,
		co.date_valid_c9 as date_c9
	from siea_s_fibrvue.v_ca_assemble_cable c
	left join liste_communes com on right(c.insee,5) = com.insee
	left join siea_s_fibrza_geo_zapm_mcr pm on st_intersects(c.geom,pm.geom)
	left join siea_s_fibrvue_v_gc_assemble_chambre ch1 on st_dwithin(st_startpoint(c.geom),ch1.geom,0.2)
	left join siea_s_fibrvue_v_gc_assemble_chambre ch2 on st_dwithin(st_endpoint(c.geom),ch2.geom,0.2)
	left join siea_s_fibrvue_v_ca_assemble_boitier b1 on st_dwithin(st_startpoint(c.geom),b1.geom,0.2)
	left join siea_s_fibrvue_v_ca_assemble_boitier b2 on st_dwithin(st_endpoint(c.geom),b2.geom,0.2)
	left join (SELECT c.ident, COUNT(s.ident) as nb_supp
			from siea_s_fibrvue_v_ca_assemble_cable c
			left join siea_s_fibrvue_v_gc_assemble_support s on st_dwithin(c.geom,s.geom,0.2)
			group by c.ident) s on s.ident = c.ident
	left join suivi_non_conformites nc on nc.cable = c.ident
	left join suivi_chambres ch on nc.siea = ch.id_siea
	left join suivi_cables_commandes cc on cc.id_cable = c.ident
	left join suivi_commandes co on co.fci = cc.fci
	where coalesce(coalesce(c.ident,'@') = id_cable, true)=true;
$$

Ici j'utilise '@' en supposant que "id_cable" ne prendra jamais le caractère @. Donc il faut adapter le code!
@+

18/01/2021, 09h42
Coco47

Bonjour merci pour votre retour.

J'ai testé et par contre ca ne marche pas vraiment. Quelque soit le paramètres que je rentre j'ai toujours toutes mes lignes qui ressortent. Et je ne suis pas sûr de bien comprendre comment c'est sensé fonctionner.

Dans le cas ou je met "id_XX" en paramètre :

Citation:

coalesce(c.ident,'@') 'ressort toujours c.ident vu que ce champ est toujours renseigné ?
coalesce(c.ident,'@') = id_cable 'là je ne suis pas sûr, ca fonctionne comme un if ? Ca ressort true ou false en fonction de du résultat de l'égalité ?
coalesce(coalesce(c.ident,'@') = id_cable, true) 'là le coalesce va toujours me ressortir la valeur de l'égalité true ou false, c'est bien ça ?

Dans le cas ou je met "NULL" en paramètre :

Citation:

coalesce(c.ident,'@') 'ressort toujours c.ident vu que ce champ est toujours renseigné ?
coalesce(c.ident,'@') = id_cable 'là je ne suis pas sûr, ca fonctionne comme un if ? Ca ressort true ou false en fonction de du résultat de l'égalité ? Et donc comme id_cable = NULL ca ressort quoi ? NULL ?
18/01/2021, 16h44
alassanediakite
Salut
La valeur donné par

Code:

coalesce(coalesce(c.ident,'@') = id_cable, true)

Nous pouvons la simplifiée par

Code:

coalesce(c.ident = id_cable, true)

par ce que "c.ident" est toujours renseigné!
- si id_cable (le paramètre) n'est pas null, c.ident=id_cable donne false alors le resultat sera false aucune ligne ne sera retourné
- si id_cable (le paramètre) n'est pas null, c.ident=id_cable donne true alors le resultat sera true les lignes correspondantes seront retournées
- si id_cable (le paramètre) est null, c.ident=id_cable donne NULL alors le resultat sera true puisque COALESCE saute le NULL toutes les lignes seront retournées
Une question...
L'objectif est-il...
1. si le paramètre n'est pas null, retourner les lignes s'il y a correspondance
2. sinon (si le paramètre est null) retourner toutes les lignes?
@+
18/01/2021, 17h26
Coco47

Merci pour les éclaircissements.

Par contre je ne comprends pas la différrence entre votre point 2 et votre point 3, dans les 2 cas la formule coalesce(c.ident = id_cable, true) retourne true. Pourquoi dans un cas on aurait que les lignes correspondante et dans l'autre toutes les lignes ?

Et donc oui mon but c'était de soit retourner la ligne correspondante soit de tout retourné si je mettais NULL en paramètre.
18/01/2021, 17h58
alassanediakite

Salut

Citation:

Envoyé par Coco47

dans les 2 cas la formule coalesce(c.ident = id_cable, true) retourne true

Non...

Code:

select coalesce(1=1,true), coalesce(1=2,true), coalesce(1=null,true)

donne...

true false true

@+
19/01/2021, 09h13
Coco47

Euh...
Je crois que j'ai du mal avec un truc.

Cas 1 : id_cable <> NULL et <> c.ident => (c.ident = id_cable) = false => coalesce(c.ident = id_cable, true) = false
Cas 2 : id_cable <> NULL et = c.ident => (c.ident = id_cable) = true => coalesce(c.ident = id_cable, true) = true
Cas 3 : id_cable = NULL => (c.ident = id_cable) = NULL => coalesce(c.ident = id_cable, true) = true

C'est bien ça ?

Donc si c'est bien ça, je comprends pas comment par la suite on peut avoir une diffférence de résultats entre le cas 2 et le cas 3.
19/01/2021, 13h10
alassanediakite

Salut
Oui le cas 2 et cas 3 donnent true.
Il y a un problème d'ordre de cas entre nous.
Mais si tu est d'accord déjà que le cas 1 chez toi donne false, alors pour ce cas la fonction ne donne aucune ligne.
@+