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.