Prendre un thé
Tapoter des doights
Monsieur fantôme
Quelle heure est-il ?
BazinGa's Tips & tuto IT
BazinGa's - Tips & tuto IT

Modifier le type de plusieurs colonnes en une seule fois

Il m’arrive régulièrement d’avoir besoin de modifier le type d’une colonne dans PostgreSQL. L’opération est simple est consiste en la requête suivante :

ALTER TABLE mon_schema.ma_table
	ALTER COLUMN ma_colonne TYPE date
	USING fonction(ma_colonne)

Cependant, j’ai très souvent le problème de devoir utiliser cette fonction sur plusieurs colonne d’un seul coup (par exemple si une même colonne existe dans plusieurs tables).

Voici donc une petite procédure qui permet de retyper plusieurs colonnes et qui poursuit sont traitement même en cas d’erreur (tout en renvoyant l’erreur dans la sortie de la BDD).

DO 
$corps$
DECLARE 
	liste_table record;

BEGIN
	
	RAISE NOTICE USING MESSAGE = 
		chr(10) || 
		$a$- - - - - - - - - - - - - - - - - - - - - - -$a$ || chr(10) || 
		$b$Démarrage traitement$b$ || chr(10) || 
		chr(10)
	;

	FOR liste_table IN (
			SELECT 
				column1 AS nom_schema,
				column2 AS nom_table,
				column3 AS nom_colonne,
				column4 AS nouveau_type,
				column5 AS ma_fonction
			FROM 
				(VALUES 
					-- Liste des colonnes à traiter
					-- Nom du schéma, nom de la table, nom de la colonne, nouveau type de la colonne, fonction de conversion à utiliser
					-- ex : ('mon_schema','ma_table','ma_colonne', 'date', 'ma_colonne::date')
					('mon_schema','ma_table_1','ma_colonne_1', 'text', 'ma_colonne::text'),
					('mon_schema','ma_table_1','ma_colonne_2', 'int', 'ma_colonne::integer'),
					('mon_schema','ma_table_2','ma_colonne', 'date', 'ma_colonne::date')
				) AS valeur
		)
		
	-- Pour chaque élément de la liste
	LOOP

		RAISE NOTICE USING MESSAGE = 
			$a$Colonne : $a$ || quote_ident( liste_table.nom_schema) || $b$.$b$ || quote_ident( liste_table.nom_table) || $c$.$c$ || quote_ident( liste_table.nom_colonne) || chr(10) ||
			$d$	Fonction : $d$ || liste_table.ma_fonction
		;

		BEGIN

			EXECUTE 
				$a$ALTER TABLE $a$ || quote_ident( liste_table.nom_schema) || $b$.$b$ || quote_ident( liste_table.nom_table) || $c$
					ALTER COLUMN $c$ || quote_ident( liste_table.nom_colonne) || $d$ TYPE $d$ || quote_ident( liste_table.nom_colonne) || $e$
					USING $e$ || liste_table.ma_fonction 
				;

			EXCEPTION WHEN OTHERS THEN
				RAISE NOTICE USING MESSAGE = 
					$a$		/!\ Erreur traitement sur la table /!\$a$ || chr(10) ||
					$b$		/!\ Code erreur : $b$ || SQLSTATE || $c$ - Message : $c$ || SQLERRM 
				;
			END;

	END LOOP;

	RAISE NOTICE USING MESSAGE = 
		chr(10) || 
		$b$Fin du traitement$b$ || chr(10) || 
		$a$- - - - - - - - - - - - - - - - - - - - - - -$a$ || chr(10) || 
		chr(10)
	;

END
$corps$

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL colonneprocéduretype

50%