Forums PostgreSQL.fr

leapb

Membre

Erreur fonction PL/pgSQL avec with ... as

Bonjour,

J'ai créé une fonction qui est comme ceci:

-- creation d'une fonction qui met à jour les données si elles sont valables et plus récentes
drop function if exists public.maj_lea_agbio_op_identification(); 
create function public.maj_lea_agbio_op_identification() 
returns varchar as $maj_lea_agbio_op_identification$
	begin  

------------------- LISTE DES ACTIONS A REALISER ---------------
	--------------------------- UPDATE DU NOUVEAU SIRET SI IL EST DIFFERENT ET VALIDE ------------------
		-- verifie s'il y a un siret dans la table op_identification qui est different du siret valide le plus recent
		if exists (select distinct ... from ... where ...) 	
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set siret = j.siret  
			 		from ... where ... ;
		end if;
	
	---------------------- UPDATE DU NOUVEAU RS SI IL EST DIFFERENT ------------------
		-- verifie s'il y a un rs dans la table op_identification qui est different du siret valide le plus recent
		if exists (select distinct ... from ... where ...) 	
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set rs= j.rs
			 		from ... where ... ;
		end if;

		return null;
	
------------ LISTE DES TABLES AVEC LES INFOS VALIDES ET PLUS RECENTES  --------
		-- enregistre dans une "table" provisoire le siret valide le plus récent
		with siret_rec as(select ... from ... group by ...),
			
		-- enregistre dans une "table" provisoire le rs valide le plus récent
		rs_rec as(select ... from ... group by ...)
			
	end;
$maj_lea_agbio_op_identification$ language plpgsql;

J'ai vu sur un forum qu'il fallait écrire la partie with... as () après le "return", ce qui m'a permis de corriger des erreurs que j'avais avant. Cependant j'ai encore cette erreur que je n'arrive pas à régler :

 Error occurred during SQL script execution

Motif:
SQL Error [42601]: ERREUR: erreur de syntaxe sur ou près de « end »
  Position : 4061 

Il s'agit du dernier end, juste avant $maj_lea_agbio_op_identification$ language plpgsql;

Je suis sur DBeaver.

rjuju

Administrateur

Re : Erreur fonction PL/pgSQL avec with ... as

Sans le code complet de la fonction, difficile à dire.  D'après votre extrait, la dernière requête est invalide car il manque une requête après la définition des CTE ainsi qu'un point virgule.

---

Julien.
https://rjuju.github.io/

leapb

Membre

Re : Erreur fonction PL/pgSQL avec with ... as

Désolée, je pensais que ça suffisait comme infos. Les CTE sont utilisées plus haut. Voici le code complet :

-- creation d'une fonction qui met à jour les données si elles sont valables et plus récentes
drop function if exists public.maj_lea_agbio_op_identification(); 
create function public.maj_lea_agbio_op_identification() 
returns varchar as $maj_lea_agbio_op_identification$
	begin  

------------------- LISTE DES ACTIONS A REALISER	---------------
-------------------------- UPDATE DU NOUVEAU SIRET SI IL EST DIFFERENT ET VALIDE ------------------
		-- verifie s'il y a un siret dans la table op_identification qui est different du siret valide le plus recent
		if exists (select distinct siret_rec.id, siret_rec.siret, translate(siret_rec.siret,'0123456789','@@@@@@@@@@')
						from siret_rec 
							left join lea_agbio_operateur_identification on siret_rec.id = lea_agbio_operateur_identification.id_operateur 
																		and lea_agbio_operateur_identification.siret = siret_rec.siret 
						where lea_agbio_operateur_identification.id_operateur is null)
		
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set siret = j.siret  
			 		from 
			 			export_br_oper
					 	left join (select distinct siret_rec.id, siret_rec.siret
						from export_br_oper 
							left join lea_agbio_operateur_identification on export_br_oper.id = lea_agbio_operateur_identification.id_operateur and lea_agbio_operateur_identification.siret = export_br_oper.siret 
							left join siret_rec on export_br_oper.id = siret_rec.id
						where translate(export_br_oper.siret,'0123456789','@@@@@@@@@@')='@@@@@@@@@@@@@@'
							and export_br_oper.siret<>'00000000000000'
							and lea_agbio_operateur_identification.id_operateur is null
							and export_br_oper.id = siret_rec.id
						 ) j on export_br_oper.id = j.id
						where j.id = lea_agbio_operateur_identification.id_operateur ;
		end if;
	
	---------------------- UPDATE DU NOUVEAU RS SI IL EST DIFFERENT ------------------
		-- verifie s'il y a un rs dans la table op_identification qui est different du rs valide le plus recent
		if exists (select distinct rs_rec.id, rs_rec.rs
						from rs_rec 
							left join lea_agbio_operateur_identification on rs_rec.id = lea_agbio_operateur_identification.id_operateur 
																		and lea_agbio_operateur_identification.rs = rs_rec.rs 
						where lea_agbio_operateur_identification.id_operateur is null)
		
		-- si oui, alors mise a jour
			then update lea_agbio_operateur_identification 
			 	set rs = j.rs  
			 		from 
			 			export_br_oper
					 	left join (select distinct rs_rec.id, rs_rec.rs
						from export_br_oper 
							left join lea_agbio_operateur_identification on export_br_oper.id = lea_agbio_operateur_identification.id_operateur and lea_agbio_operateur_identification.rs = export_br_oper.rs 
							left join rs_rec on export_br_oper.id = rs_rec.id
						where lea_agbio_operateur_identification.id_operateur is null
							and export_br_oper.id = rs_rec.id
						 ) j on export_br_oper.id = j.id
						where j.id = lea_agbio_operateur_identification.id_operateur ;
		end if;
		return null;
	
------------ LISTE DES TABLES AVEC LES INFOS VALIDES ET PLUS RECENTES --------
		-- enregistre dans une "table" provisoire le siret valide le plus récent
		with siret_rec as(
			select i.id, 
				max(i.annee) as "annee",
				max(case
						when i.row_no = 1 then i.siret
					end) as "siret"
			from (	select export_br_oper.id, export_br_oper.annee, export_br_oper.siret,
						row_number() over (partition by id order by annee desc) as "row_no"
					from export_br_oper
					where translate(export_br_oper.siret,'0123456789','@@@@@@@@@@')='@@@@@@@@@@@@@@'
						and export_br_oper.siret<>'00000000000000') i
			group by i.id),
			
		-- enregistre dans une "table" provisoire le rs valide le plus récent
		rs_rec as(
			select i.id, 
				max(i.annee) as "annee",
				max(case
						when i.row_no = 1 then i.rs
					end) as "rs"
			from (	select export_br_oper.id, export_br_oper.annee, export_br_oper.rs,
						row_number() over (partition by id order by annee desc) as "row_no"
					from export_br_oper
					where rs is not null) i
			group by i.id)
			
	end;
$maj_lea_agbio_op_identification$ language plpgsql;

gleu

Administrateur

Re : Erreur fonction PL/pgSQL avec with ... as

Comme disait Julien dans son premier commentaire, il manque un point virgule à la fin de la dernière requête ("group by i.id)").

---

Guillaume.

leapb

Membre

Re : Erreur fonction PL/pgSQL avec with ... as

Comme disait Julien dans son premier commentaire, il manque un point virgule à la fin de la dernière requête ("group by i.id)").

Merci. Je l'ai rajouté mais j'ai toujours une erreur :

 Error occurred during SQL query execution

Motif:
SQL Error [42601]: ERREUR: erreur de syntaxe à la fin de l'entrée
  Position : 4020

Quand j'enlève les tables CTE et que je les recréé à chaque fois que j'en ai besoin ça fonctionne, mais je suis pas sûre que ce soit le plus efficace et lisible comme code...

rjuju

Administrateur

Re : Erreur fonction PL/pgSQL avec with ... as

Comme également indiqué dans mon premier message, la requête est incomplète.  Grosso modo vous avez écrit

WITH s AS (SELECT ...);

Il manque donc la requête finale.  Par exemple

WITH s AS (SELECT...) SELECT * FROM s;

---

Julien.
https://rjuju.github.io/

leapb

Membre

Re : Erreur fonction PL/pgSQL avec with ... as

Il manque donc la requête finale.  Par exemple

WITH s AS (SELECT...) SELECT * FROM s;

D'accord merci. Donc si j'ai bien compris, il n'y a pas de façon de créer les tables que j'ai voulu créer pour pouvoir les utiliser plus haut dans le code ? En tout cas pas de cette manière. Car je n'ai pas besoin de faire une requête finale derrière, mon but était juste d'alléger le code et de ne pas refaire cette table intermédiaire plusieurs fois. D'où mon idée de la faire une seule fois, de lui donner un nom et de l'appeler ensuite.

rjuju

Administrateur

Re : Erreur fonction PL/pgSQL avec with ... as

Je ne suis pas certain de comprendre.  Les CTE ne sont pas des tables temporaires, et n'existent que durant l'exécution de la requête.  Vous pouvez toutefois utiliser chaque CTE plusieurs fois dans une même requête, y compris en écriture.  J'imagine que vous pouvez transformer vos requêtes en une seule, du style:

WITH siret_rec AS (...),
new_siret AS (SELECT ... FROM siret_rec JOIN ...),
update_siret AS (INSERT INTO ... SELECT ... FROM new_siret)
SELECT 1;

En faisant en sorte que chaque CTE renvoie les informations nécessaires.  Si seules des opérations en écritures dans d'autres CTE sont nécessaires, finissez par un simple "SELECT 1", il y a en effet une garantie sur PostgreSQL que toutes les CTE en écritures seront exécutées même si elle ne sont pas référencées dans la requête.

---

Julien.
https://rjuju.github.io/

leapb

Membre

Re : Erreur fonction PL/pgSQL avec with ... as

Super, j'ai mieux compris le fonctionnement des CTE et je vais essayer comme ça. Merci beaucoup