Discussion :

[Escandil]

Bonjour,

Voila, je cherche a supprimer un ensemble de tables identifiées par un préfixe commun.
En cherchant sur la FAQ, j'ai bien trouver un moyen simple de les lister

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 SELECTtablename FROM pg_tables WHERE tablename ~ 'XXX';

Mais bien entendu, il est impossible de supprimer les tables a partir de la vue (j'ai essayer un DELETE FROM pg_tables WHERE tablename ~ 'XXX'; :p)

Si vous avez des suggestions/méthodes, ça finira p-e dans la FAQ ^^

[GrandFather]

Bonjour,

et avec une chtiote procédure stockée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
...
FOR tb IN SELECT tablename FROM pg_tables WHERE tablename ~ ''XXX''
  EXECUTE ''DROP TABLE '' || quote_ident(tb)
END LOOP;
...

Sauvegarder la base avant de tenter, bien entendu...

[hpalpha]

J'allais proposer la meme solution,
merci GrandFather,
je l'ajoute dans la FAQ et pensant à la transformer en procedure stockée.

D'ailleur, je vois que l'on utilise dans l'exemple l'operateur ~ n'est ce pas un peu risqué ?
remarque, un procedure stockée interressante serait tout simplement de mettre en parametre le pattern, non ?

[Escandil]

Je vais essayer cet apres midi. Mais effectivement le XXX sera passé en parametre afin de pouvoir appeler la procédure via un script. Ce dernier est issus d'un programme delphi qui gère la valeur du préfixe.

[Escandil]

Bien j'ai essayer de mettre en place une procedure suivants vos conseils, et voila a quoi j'arrive :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE OR REPLACE FUNCTION Supression_par_prefixe(VARCHAR) RETURNS VARCHAR AS '
DECLARE
prefixe ALIAS FOR $1;
BEGIN
FOR Table_Id IN SELECT Nom_avec_Prefixe FROM pg_tables WHERE Nom_avec_Prefixe ~prefixe
  EXECUTE ''DROP TABLE '' || quote_ident(Table_Id) 
END LOOP;
RETURNS ''Done'';
END;
' LANGUAGE 'plpgsql';

J'essaye de lancer la compilation avec PgAdmin, mais il me retourne l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
ERROR:  missing ".." at end of SQL expression
CONTEXT:  compile of PL/pgSQL function "supression_par_prefixe" near line 4

Je debute en plpgsql, il y a donc sans doute de nombreuses autres fautes, et peut être de meilleures méthodes.

[GrandFather]

Il ne faut pas oublier (ce que j'ai fait ) le LOOP à la fin de la ligne du for :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FOR Table_Id IN SELECT Nom_avec_Prefixe FROM pg_tables WHERE Nom_avec_Prefixe ~ prefixe LOOP

[hpalpha]

C'est un record que ca renvoi, et en plus Nom_avec_Prefixe n'existe pas dans la table,
je proposerai plus un truc du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE OR REPLACE FUNCTION "public"."droppertables" (text) RETURNS integer AS
$body$
/* On drop les tables selon le pattern passe en parametre
   en sortie on a le nombre de tables droppées 
 */
DECLARE
 enregs record;
 compteur integer = 0 ;
BEGIN
 FOR enregs IN SELECT tablename FROM pg_tables WHERE tablename ~ $1 LOOP
  --EXECUTE ''DROP TABLE '' || quote_ident(enregs.tablename)
  compteur:=compteur+1;
 END LOOP;
 return compteur;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

ATTENTION, ce code est dangeureux !!!! je dis ca pour les novices qui lirons le post, d'ailleur j'ai mis le drop en commentaire !

De plus l'utilisation du mot prefixe n'est pas tout a fait exact, en expression reguliere le match ~ 'toto' va ressortir : toto01; toto02, mais aussi 01toto, 454toto4546, ... !!!!, il faut preciser dans le pattern ^toto, qui voudra dire commence par toto

[Escandil]

Merci beaucoup pour vos réponses, je vais essayer de mettre tout cela en place.

Juste une question concernant la sécurité, (j'ai plus de base en Oracle et je pensais retrouvé le fonctionnement sous PostGres), une telle procedure pourrait elle permettre a un utilisateur de supprimer des tables sur lesquelles il n'a pas de droit ?
Risque t il seulement de détruire ses propres tables ?

Dans mon cas, la fonction sera créée au début du script est détruite en fin, mais la réponse m'intéresse.

PS : Pour le ^ préfixe, je cherchait justement la syntaxe, mais le ^ provoque des erreurs de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
ERROR:  operator does not exist: name ^ text
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
CONTEXT:  SQL query "SELECT  tablename FROM pg_tables WHERE tablename ^  $1 "
PL/pgSQL function "droppertables" line 9 at for over select rows

[GrandFather]

De plus l'utilisation du mot prefixe n'est pas tout a fait exact, en expression reguliere le match ~ 'toto' va ressortir : toto01; toto02, mais aussi 01toto, 454toto4546, ... !!!!, il faut preciser dans le pattern ^toto, qui voudra dire commence par toto

Tout à fait, ou alors utiliser LIKE (l'utilisation des expressions régulières ne s'impose pas ici).

[Escandil]

Tir croisé pendant l'edit.

Le LIKE permet de faire tourner la fonction, mais ne drop pas de table.
D'ailleur le SELECT ne renvoie rien :-/

Enfin je vais mettre le post en résolu, car leprincipal probleme est réglé, je noterai mes éventuelles découvertes à la suite (pour info) et j'apprécierai tout de même vos réponses

En tout cas, un grand merci a tous les deux pour votre aide.

[hpalpha]

La requete est
SELECT tablename FROM pg_tables WHERE tablename ~ $1

ton argument est '^toto'

donc en gros

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT droppertables('^toto');

pour le like regarde ce post
http://www.developpez.net/forums/viewtopic.php?t=388635

il doit te manquer le %

[Escandil]

Bon j'apprends pas mal de trucs grace à vous ^^

Donc pour le ^ ça marche effectivement, donc en laissant dans la procedure le $1 et le ^ dans mon script auto.

J'ai réussi a faire marcher le like aussi du coup je n'avais pas mis de quote

Merci pour votre aide une fois de plus