Convertir globalement toutes les colonnes de types texte d'un schéma

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Convertir globalement toutes les colonnes de types texte d'un schéma

Message par Phil »

Merci à Yves pour sa question :

J'ai un schéma test qui contient tous mes objets applicatifs provenant d'un passage d'Oracle à PostgreSQL. J'aimerais uniformiser tous les champs contenant des chaînes de caractères avec un seul type de données, text, et ne plus avoir de varchar, char etc.


Réponse : pas de problème pour la conversion mais attention aux vues et règles dépendant de ces tables. Ici, comme toutes les tables à modifier et toutes les vues et règles qui en dépendent appartiennent au même schéma "test", cela va faciliter les choses.
Il faut une partie détruisant les vues, une partie faisant les alter et une partie recréant les vues. Exemple (à adapter) :

Code : Tout sélectionner

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 ligne)

copy (select 'drop view if exists ' || vtu.view_schema || '.' || vtu.view_name || ' cascade ;'  from information_schema.view_table_usage vtu where vtu.table_schema = 'test') to '/tmp/drop_test_views.sql';

copy (select distinct 'alter table ' || c.table_name || ' alter column ' || c.column_name || ' type text;' 
from information_schema.columns c join information_schema.tables t on (t.table_name = c.table_name and t.table_schema = c.table_schema)
where t.table_schema = 'test' 
and t.table_type in ('BASE TABLE', 'FOREIGN TABLE')
and c.data_type in ('character', 'character varying'))
to '/tmp/alter_test_char_columns.sql';

pg_dump $(psql --tuples-only -c "select string_agg('-t ' || vtu.view_schema || '.'  || vtu.view_name, ' ') from information_schema.view_table_usage vtu where vtu.table_schema = 'test'") > /tmp/create_test_views.sql
Regrouper les 3 scripts générés dynamiquement en 1 seul script en ajoutant un start transaction; au début du script et un commit; en fin de script pour tout jouer en une transaction.

Note 1 : il serait possible de supprimer uniquement les vues dépendant des tables modifiées plutôt que supprimer sans restriction les vues dépendant des tables du schéma
Note 2 : si toutes les dépendances n'étaient pas cantonnées au schema test, il faudrait les gérer histoire de ne pas avoir de mauvaises surprises en détruisant des vues en cascade sans avoir généré de quoi les recréer. Pour simplifier, il est aussi possible de travailler avec un script détruisant/recréant toutes les vues de la base.
Cdlt. Phil - pgphil.ovh
Répondre