Migration d'une procédure stockée depuis SQL serverr

**mercure07** · 26/12/2011, 17h18

Bonjour tout le monde,
Je suis entrain de migrer une procédure stockée depuis SQL Server à PostgreSQL.
Le code de la procédure est le suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
ALTER PROCEDURE [theriaque].[GET_THE_GTIAM_ID] 
  @LISTESP VARCHAR(255),
  @GRAVITE INT
AS
DECLARE	@IT1SP_FIT_CODE_FK_PK INT		
DECLARE	@CDF_NOM VARCHAR(250)  		
DECLARE	@SP_NOM  VARCHAR(250)		
DECLARE	@SP_NOM2 VARCHAR(250) 		
DECLARE	@IT1SP_SP_CODE_FK_PK INT		
DECLARE	@IT2SP_SP_CODE_FK_PK INT		
DECLARE	@FITNA_CDF_NAIT_CODE_FK_PK INT	
DECLARE	@idcombinaison VARCHAR(250) 
DECLARE @row_count INT
 
create TABLE #tmp_gtiam1( 
	IDINTER INT,
	VALIDE VARCHAR(250),
	TERME_1 VARCHAR(250),
	TERME_2 VARCHAR(250),
	ID_T1 INT,
	ID_T2 INT,
	NIVEAU INT,
	comb VARCHAR(250) 
)
 
create TABLE #tmp_SP( 
	SP INT 
)
 
 insert into #tmp_SP(
      [SP]) exec [theriaque].[Core_SP_SplitString] @LISTESP, ','
 
--select * from #tmp_SP;
 
DECLARE curseur1 CURSOR FOR
	SELECT  t1.IT1SP_FIT_CODE_FK_PK AS IDINTER , 		
		t5.CDF_NOM AS VALIDE,  		
		t6.SP_NOM  AS TERME_1, 		
		t7.SP_NOM  AS TERME_2, 	
		t1.IT1SP_SP_CODE_FK_PK AS ID_T1, 		
		t2.IT2SP_SP_CODE_FK_PK AS ID_T2, 	
		t3.FITNA_CDF_NAIT_CODE_FK_PK 	AS NIVEAU  ,	
		CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK 
			THEN CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
			ELSE CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
			END AS combinaison 
		FROM   theriaque.IT1SP_TERME1SPECIALITE   t1,		
		theriaque.IT2SP_TERME2SPECIALITE   t2,		
		theriaque.FITNA_INTERACTION_NATURE t3,		
		theriaque.FITVA_NIVEAU_VALIDATION  t4,		
		theriaque.CDF_CODIF                t5,		    
		theriaque.SP_SPECIALITE            t6,		
		theriaque.SP_SPECIALITE            t7 		
		WHERE  t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK 		
		AND    t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t5.CDF_CODE_PK          = t4.FITVA_CDF_VAIT_CODE_FK_PK   		
		AND    t6.SP_CODE_SQ_PK        = t1.IT1SP_SP_CODE_FK_PK  		
		AND    t7.SP_CODE_SQ_PK        = t2.IT2SP_SP_CODE_FK_PK  		
		AND    t4.FITVA_CDF_VAIT_CODE_FK_PK = 1 		
		AND    t5.CDF_NUMERO_PK        = 'IV'		
		AND    t1.IT1SP_SP_CODE_FK_PK  <> t2.IT2SP_SP_CODE_FK_PK 		
		AND    t1.IT1SP_SP_CODE_FK_PK  IN (select SP from #tmp_SP) 	
		AND    t2.IT2SP_SP_CODE_FK_PK  IN (select SP from #tmp_SP) 		
		AND    t3.FITNA_CDF_NAIT_CODE_FK_PK  <= @GRAVITE				
		AND    EXISTS ( SELECT 1,5		/* IT1SP_FIT_CODE_FK_PK , IT1SP_SP_CODE_FK_PK*/
						FROM   theriaque.IT1SP_TERME1SPECIALITE 		
						WHERE  t2.IT2SP_SP_CODE_FK_PK  = IT1SP_SP_CODE_FK_PK  		
						AND    t1.IT1SP_FIT_CODE_FK_PK = IT1SP_FIT_CODE_FK_PK ) 	
		UNION 		
		SELECT  t1.IT1SP_FIT_CODE_FK_PK AS IDINTER ,		
		t5.CDF_NOM AS VALIDE,  		
		t6.SP_NOM  AS TERME_1, 		
		t7.SP_NOM  AS TERME_2, 		
		t1.IT1SP_SP_CODE_FK_PK AS ID_T1,		
		t2.IT2SP_SP_CODE_FK_PK AS ID_T2,		
		t3.FITNA_CDF_NAIT_CODE_FK_PK 	AS NIVEAU  ,	
		CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK 
			THEN CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
			ELSE CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
			END AS combinaison 
		FROM   theriaque.IT1SP_TERME1SPECIALITE   t1,		
		theriaque.IT2SP_TERME2SPECIALITE   t2,		
		theriaque.FITNA_INTERACTION_NATURE t3,		
		theriaque.FITVA_NIVEAU_VALIDATION  t4,		
		theriaque.CDF_CODIF                t5,		
		theriaque.SP_SPECIALITE            t6,		
		theriaque.SP_SPECIALITE            t7 		
		WHERE  t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK 		
		AND    t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t5.CDF_CODE_PK          = t4.FITVA_CDF_VAIT_CODE_FK_PK   		
		AND    t6.SP_CODE_SQ_PK        = t1.IT1SP_SP_CODE_FK_PK 		
		AND    t7.SP_CODE_SQ_PK        = t2.IT2SP_SP_CODE_FK_PK 		
		AND    t4.FITVA_CDF_VAIT_CODE_FK_PK = 1 		
		AND    t5.CDF_NUMERO_PK        = 'IV'		
		AND    t1.IT1SP_SP_CODE_FK_PK  <> t2.IT2SP_SP_CODE_FK_PK        		
		AND    t1.IT1SP_SP_CODE_FK_PK  IN (select SP from #tmp_SP) 		
		AND    t2.IT2SP_SP_CODE_FK_PK  IN (select SP from #tmp_SP) 		
		AND    t3.FITNA_CDF_NAIT_CODE_FK_PK  <= @GRAVITE		
		AND    NOT EXISTS ( SELECT  1,5		
							FROM   theriaque.IT1SP_TERME1SPECIALITE 		
							WHERE  t2.IT2SP_SP_CODE_FK_PK  = IT1SP_SP_CODE_FK_PK  		
							AND    t1.IT1SP_FIT_CODE_FK_PK = IT1SP_FIT_CODE_FK_PK)	
	ORDER BY 1 
 
OPEN curseur1
FETCH NEXT FROM curseur1 
INTO @IT1SP_FIT_CODE_FK_PK ,		
	@CDF_NOM , 		
	@SP_NOM  ,		
	@SP_NOM2  ,	
	@IT1SP_SP_CODE_FK_PK ,		
	@IT2SP_SP_CODE_FK_PK ,		
	@FITNA_CDF_NAIT_CODE_FK_PK ,	
	@idcombinaison 
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @row_count = COUNT(*) FROM #tmp_gtiam1 WHERE comb = @idcombinaison
	IF(@row_count=0)
	BEGIN
	INSERT #tmp_gtiam1 SELECT @IT1SP_FIT_CODE_FK_PK ,		
								@CDF_NOM , 		
								@SP_NOM  ,		
								@SP_NOM2  ,	
								@IT1SP_SP_CODE_FK_PK ,		
								@IT2SP_SP_CODE_FK_PK ,		
								@FITNA_CDF_NAIT_CODE_FK_PK ,	
								@idcombinaison 
	END
FETCH NEXT FROM curseur1 INTO @IT1SP_FIT_CODE_FK_PK ,		
	@CDF_NOM , 		
	@SP_NOM  ,		
	@SP_NOM2  ,	
	@IT1SP_SP_CODE_FK_PK ,		
	@IT2SP_SP_CODE_FK_PK ,		
	@FITNA_CDF_NAIT_CODE_FK_PK ,	
	@idcombinaison  
 
END
CLOSE curseur1
select IDINTER, VALIDE, TERME_1, TERME_2, ID_T1, ID_T2, NIVEAU from #tmp_gtiam1
DROP TABLE #tmp_gtiam1
DROP TABLE #tmp_SP
DEALLOCATE curseur1'

La partie que je n'ai pas pu migrer commence à partir du "OPEN curseur1......" (ligne 107) lorsque je dois parcourir tout le curseur et insérer les champs dans la table #tmp_gtiam.
Est ce que quelqu'un pourrait m'éclaircir et m'aider à faire effectuer ce traitement sous PostgreSQL
Merci pour votre collaboration d'avance.
Bien cordialement.

**estofilo** · 26/12/2011, 17h57

Pour un parcours de curseur dans une boucle en plpgsql tu peux t'inspirer du code ci-dessous:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
DECLARE
  c cursor for select table_schema,table_name from information_schema.tables;
  a text;
  b text;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO a,b;
    EXIT WHEN NOT FOUND;
    raise notice '%,%', a, b;
  END LOOP;
END;

**mercure07** · 27/12/2011, 11h11

Merci pour votre réponse. J'ai essayé d'écrire la fonction en s'inspirant du modèle. Ci-après ce qui j'ai fait.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SP_CODE_FK_PK AS ID_T2, 	
		t3.FITNA_CDF_NAIT_CODE_FK_PK 	AS NIVEAU  ,	
		CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK 
			THEN t1.IT1SP_FIT_CODE_FK_PK || '-' || t1.IT1SP_SP_CODE_FK_PK || '-' || t2.IT2SP_SP_CODE_FK_PK || '-' || t3.FITNA_CDF_NAIT_CODE_FK_PK
			ELSE t1.IT1SP_FIT_CODE_FK_PK || '-' || t2.IT2SP_SP_CODE_FK_PK || '-' || t1.IT1SP_SP_CODE_FK_PK || '-' || t3.FITNA_CDF_NAIT_CODE_FK_PK
			END AS combinaison 
		FROM theriaque.IT1SP_TERME1SPECIALITE   t1,		
		theriaque.IT2SP_TERME2SPECIALITE   t2,		
		theriaque.FITNA_INTERACTION_NATURE t3,		
		theriaque.FITVA_NIVEAU_VALIDATION  t4,		
		theriaque.CDF_CODIF                t5,		    
		theriaque.SP_SPECIALITE            t6,		
		theriaque.SP_SPECIALITE            t7 		
		WHERE  t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK 		
		AND    t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t5.CDF_CODE_PK          = t4.FITVA_CDF_VAIT_CODE_FK_PK   		
		AND    t6.SP_CODE_SQ_PK        = t1.IT1SP_SP_CODE_FK_PK  		
		AND    t7.SP_CODE_SQ_PK        = t2.IT2SP_SP_CODE_FK_PK  		
		AND    t4.FITVA_CDF_VAIT_CODE_FK_PK = '1' 		
		AND    t5.CDF_NUMERO_PK        = 'IV'		
		AND    t1.IT1SP_SP_CODE_FK_PK  <> t2.IT2SP_SP_CODE_FK_PK 		
		AND    t1.IT1SP_SP_CODE_FK_PK  IN (13183, 15521) 	
		AND    t2.IT2SP_SP_CODE_FK_PK  IN (13183, 15521) 		
		AND    t3.FITNA_CDF_NAIT_CODE_FK_PK  <= '11'				
		AND    EXISTS ( SELECT 1 , 5
						FROM   theriaque.IT1SP_TERME1SPECIALITE t8		
						WHERE  t2.IT2SP_SP_CODE_FK_PK  = t8.IT1SP_SP_CODE_FK_PK  		
						AND    t1.IT1SP_FIT_CODE_FK_PK = t8.IT1SP_FIT_CODE_FK_PK ) 	
		UNION 		
		SELECT  t1.IT1SP_FIT_CODE_FK_PK AS IDINTER ,		
		t5.CDF_NOM AS VALIDE,  		
		t6.SP_NOM  AS TERME_1, 		
		t7.SP_NOM  AS TERME_2, 		
		t1.IT1SP_SP_CODE_FK_PK AS ID_T1,		
		t2.IT2SP_SP_CODE_FK_PK AS ID_T2,		
		t3.FITNA_CDF_NAIT_CODE_FK_PK 	AS NIVEAU  ,	
		CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK 
			THEN t1.IT1SP_FIT_CODE_FK_PK || '-' || t1.IT1SP_SP_CODE_FK_PK || '-' || t2.IT2SP_SP_CODE_FK_PK || '-' || t3.FITNA_CDF_NAIT_CODE_FK_PK
			ELSE t1.IT1SP_FIT_CODE_FK_PK || '-' || t2.IT2SP_SP_CODE_FK_PK || '-' || t1.IT1SP_SP_CODE_FK_PK || '-' || t3.FITNA_CDF_NAIT_CODE_FK_PK
			END AS combinaison 
		FROM theriaque.IT1SP_TERME1SPECIALITE   t1,		
		theriaque.IT2SP_TERME2SPECIALITE   t2,		
		theriaque.FITNA_INTERACTION_NATURE t3,		
		theriaque.FITVA_NIVEAU_VALIDATION  t4,		
		theriaque.CDF_CODIF                t5,		
		theriaque.SP_SPECIALITE            t6,		
		theriaque.SP_SPECIALITE            t7 		
		WHERE  t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK 		
		AND    t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK 		
		AND    t5.CDF_CODE_PK          = t4.FITVA_CDF_VAIT_CODE_FK_PK   		
		AND    t6.SP_CODE_SQ_PK        = t1.IT1SP_SP_CODE_FK_PK 		
		AND    t7.SP_CODE_SQ_PK        = t2.IT2SP_SP_CODE_FK_PK 		
		AND    t4.FITVA_CDF_VAIT_CODE_FK_PK = '1' 		
		AND    t5.CDF_NUMERO_PK        = 'IV'		
		AND    t1.IT1SP_SP_CODE_FK_PK  <> t2.IT2SP_SP_CODE_FK_PK        		
		AND    t1.IT1SP_SP_CODE_FK_PK  IN (13183, 15521) 		
		AND    t2.IT2SP_SP_CODE_FK_PK  IN (13183, 15521) 		
		AND    t3.FITNA_CDF_NAIT_CODE_FK_PK  <= '11'		
		AND    NOT EXISTS ( SELECT 1 , 5	
							FROM    theriaque.IT1SP_TERME1SPECIALITE t8		
							WHERE  t2.IT2SP_SP_CODE_FK_PK  = t8.IT1SP_SP_CODE_FK_PK  		
							AND    t1.IT1SP_FIT_CODE_FK_PK = t8.IT1SP_FIT_CODE_FK_PK)	
	ORDER BY 1 ;
BEGIN
OPEN queryCursor;
LOOP 
	FETCH queryCursor INTO IT1SP_FIT_CODE_FK_PK ,		
	CDF_NOM , 		
	SP_NOM  ,		
	SP_NOM2  ,	
	IT1SP_SP_CODE_FK_PK ,		
	IT2SP_SP_CODE_FK_PK ,		
	FITNA_CDF_NAIT_CODE_FK_PK ,	
	idcombinaison;
    EXIT WHEN NOT FOUND;
	SELECT COUNT(*) AS COUNT_RECORDS FROM tmp_gtiam WHERE comb = idcombinaison;
	IF(COUNT_RECORDS=0)
	THEN
	INSERT into tmp_gtiam SELECT IT1SP_FIT_CODE_FK_PK ,		
								CDF_NOM , 		
								SP_NOM  ,		
								SP_NOM2  ,	
								IT1SP_SP_CODE_FK_PK ,		
								IT2SP_SP_CODE_FK_PK ,		
								FITNA_CDF_NAIT_CODE_FK_PK ,	
								idcombinaison ;
	END IF;    
END LOOP;
OPEN curRet FOR 
    	SELECT IT1SP_FIT_CODE_FK_PK, CDF_NOM, SP_NOM, SP_NOM2,	IT1SP_SP_CODE_FK_PK, IT2SP_SP_CODE_FK_PK, FITNA_CDF_NAIT_CODE_FK_PK FROM tmp_gtiam;
  RETURN curRet;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

La fonction est exécutée correctement mais je n'arrive pas à afficher les résultats. J'ai l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
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_the_gtiam_id », ligne 97 à instruction SQL

**estofilo** · 27/12/2011, 13h24

Sur cette ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) AS COUNT_RECORDS FROM tmp_gtiam WHERE comb = idcombinaison;

Il faut un select INTO pour récupérer la colonne dans une variable, donc plutôt

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COUNT(*) INTO COUNT_RECORDS FROM tmp_gtiam WHERE comb = idcombinaison;

**mercure07** · 28/12/2011, 09h37

Merci pour votre aide, ça fonctionne correctement maintenant.
J'ai juste 2 points à compléter :
1. Le 1er point concerne le 1er paramètres d'entrées de la procédure qui est de type "text". Mais lorsque je saisi 13183,15521, j'ai l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND    t1.IT1SP_SP_CODE_FK_PK  IN (LISTSPE)

Mais là j'ai une erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ERREUR:  l'opérateur n'existe pas : integer = character varying
LINE 1: ...t2.IT2SP_SP_CODE_FK_PK AND t1.IT1SP_SP_CODE_FK_PK IN ( $1 ) ...

2. Le 2ème point c'est que j'ai utilisé une table temporaire et je dois la supprimer à la fin du traitement. J'ai placé le drop table aprés le return mais la table n'a pas été supprimé. Comment faire pour nettoyer la base des tables temporaires et curseurs.
Cordialement.

**estofilo** · 28/12/2011, 15h13

Envoyé par mercure07

1. Le 1er point concerne le 1er paramètres d'entrées de la procédure qui est de type "text". Mais lorsque je saisi 13183,15521, j'ai l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND    t1.IT1SP_SP_CODE_FK_PK  IN (LISTSPE)

Mais là j'ai une erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ERREUR:  l'opérateur n'existe pas : integer = character varying
LINE 1: ...t2.IT2SP_SP_CODE_FK_PK AND t1.IT1SP_SP_CODE_FK_PK IN ( $1 ) ...

C'est parce que le paramètre étant interprété comme une seule valeur, il ne peut être utilisé dans la requête qu'aux endroits où il y a syntaxiquement une seule valeur littérale, ce qui n'est pas le cas de IN().

Une bonne solution serait de passer un type int[] à la fonction plutôt qu'une chaine de caractères, ce qui permet de l'utiliser dans la fonction de manière propre en remplaçant IN par l'opérateur ANY.
Par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE FUNCTION exemple(param int[]) RETURNS SETOF text AS $$
BEGIN
 SELECT a INTO b FROM table WHERE colonne_int=ANY(param);
...

Ensuite la syntaxe pour exprimer une liste d'entiers en tableau est du type ARRAY[12,3,4]

Migration d'une procédure stockée depuis SQL serverr

PostgreSQL

Vue hybride

Discussions similaires

Partager

Partager