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.nouveau_type) || $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
