Forums PostgreSQL.fr

Yoyoda08

Membre

requête ALTER ne fonctionne pas dans PL/pgsql mais fonctionne en SQL

Bonjour,

Alors tout d'abord, milles excuses je suis hyper débutant en postgresql et je ne suis informaticien ni de métier ni de formation.

Alors j'explique mon cas :

Par erreur, j'ai monté une base de données avec des noms de colonne en majuscule (oui je sais, c'est nul) et celle-ci comporte 1788 colonnes. Je ne me sentais pas de les renommer à la main donc je me suis dit "faisons ça par fonction avec un curseur". Sauf que si ça marche pour le remplissage automatique des tables à partir des fichiers csv sources (les données viennent d'ACCESS), pour les ALTER TABLE ça ne marche pas.

j'ai utilisé la fonction suivante :

-------DEBUT

-- DROP FUNCTION maj_to_min_nom_colonnes();
CREATE OR REPLACE FUNCTION maj_to_min_nom_colonnes() RETURNS SETOF text AS $$

DECLARE
        curseur1 refcursor;
    curseur2 refcursor;
    chemin varchar;
    tabledest varchar;
    table_travail varchar;
    libelle_colonne_maj varchar;
    libelle_colonne_min varchar;
    requete varchar;
BEGIN
    OPEN curseur1 FOR SELECT DISTINCT nom_table FROM public.dico;
    <<boucle_tables>>
    LOOP
        FETCH curseur1 INTO table_travail;
        EXIT boucle_tables WHEN NOT FOUND;
        OPEN curseur2 FOR SELECT DISTINCT nom_champ FROM public.dico WHERE public.dico.nom_table=table_travail;
        <<boucle_colonnes>>
        LOOP
            FETCH curseur2 INTO libelle_colonne_maj;
            EXIT boucle_colonnes WHEN NOT FOUND;
            tabledest:='public.'|| table_travail;
            libelle_colonne_min:=lower(libelle_colonne_maj);
            EXECUTE 'ALTER TABLE '|| tabledest ||' RENAME COLUMN "'|| libelle_colonne_maj ||'" TO ' || libelle_colonne_min;
        END LOOP;
        CLOSE curseur2;
    END LOOP;
    CLOSE curseur1;
END;
$$
LANGUAGE plpgsql;

BEGIN
    SELECT maj_to_min_nom_colonnes();
END

-----FIN

Sauf que ça marche pas, il me dit que la colonne "LIBELLE_COLONNE" n'existe pas (malgré les " dans la requête).

J'essaye la commande SQL générée directement (par copier /coller) et... ça fonctionne !!!!

Alors bon, j'ai rusé, j'ai fait autrement :

Je créé une table requete_auto_generee avec une colonne varchar

Je lance la fonction suivante :

--------------DEBUT

-- DROP FUNCTION maj_to_min_nom_colonnes();
CREATE OR REPLACE FUNCTION maj_to_min_nom_colonnes() RETURNS SETOF text AS $$

DECLARE
        curseur1 refcursor;
    curseur2 refcursor;
    chemin varchar;
    tabledest varchar;
    table_travail varchar;
    libelle_colonne_maj varchar;
    libelle_colonne_min varchar;
    requete varchar;
BEGIN
    OPEN curseur1 FOR SELECT DISTINCT nom_table FROM public.dico;
    <<boucle_tables>>
    LOOP
        FETCH curseur1 INTO table_travail;
        EXIT boucle_tables WHEN NOT FOUND;
        OPEN curseur2 FOR SELECT DISTINCT nom_champ FROM public.dico WHERE public.dico.nom_table=table_travail;
        <<boucle_colonnes>>
        LOOP
            FETCH curseur2 INTO libelle_colonne_maj;
            EXIT boucle_colonnes WHEN NOT FOUND;
            tabledest:='public.'|| table_travail;
            libelle_colonne_min:=lower(libelle_colonne_maj);
            libelle_colonne_maj='"'|| libelle_colonne_maj ||'"';
            requete:='(''ALTER TABLE '|| tabledest ||' RENAME COLUMN '|| libelle_colonne_maj ||' TO '|| libelle_colonne_min ||';'')';
            EXECUTE format('INSERT INTO %s VALUES %s','public.requete_auto_generee',requete);
            -- EXECUTE 'ALTER TABLE '|| tabledest ||' RENAME COLUMN "'|| libelle_colonne_maj ||'" TO ' || libelle_colonne_min;
        END LOOP;
        CLOSE curseur2;
    END LOOP;
    CLOSE curseur1;
END;
$$
LANGUAGE plpgsql;

BEGIN
    SELECT maj_to_min_nom_colonnes();
END

-------------FIN

J'ai une table avec 1788 requêtes ALTER TABLE, je copie colle le résultat dans un classeur LibreOffice (pour virer les guillemets dues au type varchar), je sauve sous csv sans les guillemets en délimiteur de texte.

J'ouvre une nouvelle requête à partir de ce fichier et... ça fonctionne  (à une colonne près, qui semble rétive mais bon).

Alors OK ça fonctionne mais :

1) je n'aime pas ne pas savoir ce qui ne marche pas
2) c'est quand même (à mon avis) pas très élégant et ça relève de la bidouille

Qu'est ce que j'ai loupé dans tout ça ?

D'avance merci

PS dans la première version, j'avais utilisé aussi EXECUTE format('ALTER TABLE %s ... mais je n'ai pas sauvegardé cette version, qui ne marchait de toute façon pas (même erreur)

Dernière modification par Yoyoda08 (19/12/2016 22:15:44)

gleu

Administrateur

Re : requête ALTER ne fonctionne pas dans PL/pgsql mais fonctionne en SQL

La première fonction a l'air bonne. Aucune idée pourquoi elle ne fonctionne pas. Il faudrait plus de détails pour creuser (comme le vrai message d'erreur pour commencer, mais aussi un exemple de table où elle ne fonctionne pas).

---

Guillaume.

Yoyoda08

Membre

Re : requête ALTER ne fonctionne pas dans PL/pgsql mais fonctionne en SQL

Bonsoir,

Tout d'abord merci pour ta réponse.

Bon vu que ça a fonctionné avec la deuxième méthode, pour reproduire l'erreur il faut que je refasse le chemin inverse
Vu l'heure, je fais ça demain et j'envoie les messages exacts.

A noter que la colonne qui bug sur la première méthode n'est pas la même que celle qui bloque sur la deuxième méthode. Et le message d'erreur lors d'une exécution sous pgAdminIII (j'ai oublié de préciser que je l'utilisais) est assez flou.

Je poste ça demain.

Bonne soirée

Yoyoda08

Membre

Re : requête ALTER ne fonctionne pas dans PL/pgsql mais fonctionne en SQL

Re !

Alors j'ai refait la manip (en sens inverse, c'est à dire en essayant de passer le libellé de colonne de minuscule à majuscule) avec la première méthode, et j'obtiens le message suivant :

[WARNING  ] SELECT maj_to_min_nom_colonnes()
            ERREUR:  la colonne « r_dat_maj » n'existe pas
            CONTEXTE : instruction SQL « ALTER TABLE public.r_cat_tarif RENAME COLUMN r_dat_maj TO "R_DAT_MAJ" »
            fonction PL/pgsql maj_to_min_nom_colonnes(), ligne 28 à EXECUTE

Alors vu qu'hier j'ai eu ce problème avec la deuxième méthode (je sais pas pourquoi cette colonne de cette table en particulier pose problème, nom correct, pas d'espace, bref, ce n'est qu'un détail) ça veut dire que dans ce sens ça fonctionne.
Je fais un essai sur une seule table :

DROP FUNCTION maj_to_min_nom_colonnes();
CREATE OR REPLACE FUNCTION maj_to_min_nom_colonnes() RETURNS SETOF text AS $$

DECLARE
        curseur1 refcursor;
    curseur2 refcursor;
    chemin varchar;
    tabledest varchar;
    table_travail varchar;
    libelle_colonne_maj varchar;
    libelle_colonne_min varchar;
    requete varchar;
BEGIN
    OPEN curseur1 FOR SELECT DISTINCT nom_table FROM public.dico WHERE nom_table='r_ano_dist';
    <<boucle_tables>>
    LOOP
        FETCH curseur1 INTO table_travail;
        EXIT boucle_tables WHEN NOT FOUND;
        OPEN curseur2 FOR SELECT DISTINCT nom_champ FROM public.dico WHERE public.dico.nom_table=table_travail;
        <<boucle_colonnes>>
        LOOP
            FETCH curseur2 INTO libelle_colonne_maj;
            EXIT boucle_colonnes WHEN NOT FOUND;
            tabledest:='public.'|| table_travail;
            libelle_colonne_min:=lower(libelle_colonne_maj);
            libelle_colonne_maj='"'|| libelle_colonne_maj ||'"';
            -- requete:='(''ALTER TABLE '|| tabledest ||' RENAME COLUMN '|| libelle_colonne_maj ||' TO '|| libelle_colonne_min ||';'')';
            -- EXECUTE format('INSERT INTO %s VALUES %s','public.requete_auto_construite',requete);
            EXECUTE 'ALTER TABLE '|| tabledest ||' RENAME COLUMN '|| libelle_colonne_min ||' TO ' || libelle_colonne_maj;
        END LOOP;
        CLOSE curseur2;
    END LOOP;
    CLOSE curseur1;
END;
$$
LANGUAGE plpgsql;

BEGIN
    SELECT maj_to_min_nom_colonnes();
END

et... CA FONCTIONNE ????

je refait l'inverse et... ça marche ! A n'y rien comprendre. Pourtant les guillemets étaient bonnes (je n'y ai pas touché) les colonnes aussi. Par contre je me demande si au niveau des espace entre les clauses et les variables il n'y aurait pas eu un hic...

En tout cas désolé, je creuse si je trouve le pourquoi je donnerai la réponse. Je creuse encore pour cette unique colonne qui elle ne passe toujours pas.

Encore merci.