Forums PostgreSQL.fr

d_light

Membre

Récupération sous forme d'export table et data sans indexs

Bonjour,

   Aprés avoir parcouru les options de pg_dump, peut être que je me trompe; il me semble ne pas avoir vu la possibilité d'exporter juste les datas et les ordres de créations de tables sans les indexs et les contraintes ?. Est ce possible autrement ?

   Même chose j'aimerai faire un script capable de récupérer les ordres de création des contraintes et des indexs. Sous oracle j'avai la possibilité de récupérer ces ordres un par un via une fonction qui s'appellai get_dll. Existe t'il un équivalent sous Postgre ?

   Merci a vous pour votre aide.

Marc Cousin

Membre

Re : Récupération sous forme d'export table et data sans indexs

Bonjour,

Il s'agit des fonctions en pg_get_xxxxdef

Par exemple pg_get_indexdef ou pg_get_constraintdef

Il n'y en a pas pour tous les types malheureusement (pas de pg_get_tabledef).

---

Marc.

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

Merci Marc,

  ça va pas être mal pour récupérer les ordres des indexs je regarde de suite, par contre pour l'export sans contraintes et sans indexs c'est possible ?

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

C'est uniquement possible à partir de la version 9.2, avec le paramètre --section, en excluant "post-data".

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

arf, une raison de plus pour pousser au dessus a passer en 9. Merci quand même.

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

CREATE OR REPLACE FUNCTION recup_index()
RETURNS oidfinal AS '
DECLARE
test CURSOR IS SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
oidfinal varchar;
BEGIN
oid=0;
for oid in test loop
 select pg_get_indexdef(oid) into oidfinal;
end loop;
RETURN;
end;'
oidfinal LANGUAGE plpgsql;

Voici une petite procédure PLSQL avec l'idée de pouvoir récupérer l'ensemble des ordres sql DDL des indexs, simplement cela ne fonctionne pas. J'ai essayé de m'adapter aux spécificités du PL utilisé par POSTGRE pour l'instant je ne m'en sors pas.

J'avou ne pas bien comprendre la commande suivante: oidfinal LANGUAGE plpgsql; ?

Pour pouvoir afficher le résultat ligne par ligne sur l'ecran lors du parcours du curseur j'utilisai la fonction dbms_output.put_line(oidfinal) : une idée d'une équivalence sur POSTGRE ?

J'obtiens l'erreur suivante pour le moment:

ERROR:  syntax error at or near "CREATE"
LINE 14: CREATE OR REPLACE FUNCTION recup_index()

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

Le RETURNS donne le type de donnée retournée, dans votre cas varchar et non oidfinal. Il faut également faire un "RETURN oidfinal;", et enlever le oidfinal de la dernière ligne.

De plus, votre code ne renverra que la définition du dernier index trouvé. Vous pouvez soit retourner un tableau de varchar, soit concaténer tous les retour de pg_get_indexdef.

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

CREATE OR REPLACE FUNCTION recup_index()
RETURNS varchar AS '
DECLARE
test CURSOR IS SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
oidfinal varchar;
BEGIN
oid=0;
for oid in test loop
 select pg_get_indexdef(oid) into oidfinal;
 return oidfinal;
end loop;
end;'
LANGUAGE plpgsql;

Je ne comprend pas alors l'interet du curseur qui est de normalement ici récupérer l'ensemble des lignes qui m'interesse, ici je fais le return dans la boucle for. J'aimerai l'afficher et le récupére ensuite dans un fichier, n'y a t'il pas sous postgre une fonction pour afficher chaque ligne qui tour a tour sont récupérés dans la variable oidfinal ?

L'utilisation d'un tableau vous parait alors indispensable pour faire ce que je désire ?

Merci.

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

Le plus simple si vous voulez l'exporter est d'utiliser COPY:

COPY (SELECT pg_get_indexdef(oid) FROM SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes))) TO '/path/fichier';

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

CREATE OR REPLACE FUNCTION recup_index()
RETURNS varchar AS '
DECLARE
test CURSOR IS SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
oidfinal varchar;
BEGIN
oid=0;
for oid in test loop
COPY (SELECT pg_get_indexdef(oid)) TO '/home/xxxxx/test2.copy';
end loop;
return oidfinal;
end;'
LANGUAGE plpgsql;

ça ne passe toujours pas:

ERROR:  syntax error at or near "/"
LINE 10: COPY (SELECT pg_get_indexdef(oid)) TO  '/home/xxxxx...
                                                                           ^
J'ai tenté avec des parenthéses, des slashs et même des guillemets ... rien a faire. Avec des guillemets j'obtiens l'erreur suivante:

ERROR:  syntax error at or near "$1"
LINE 1:   $1
          ^
QUERY:    $1
CONTEXT:  SQL statement in PL/PgSQL function "recup_index" near line 7

Toujours aussi peu explicite.

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

Le problème vient de la déclaration. Je vous conseille la syntaxe suivante (remplacer les ' par $$ pour le corps de la fonction):

CREATE OR REPLACE FUNCTION recup_index()
RETURNS varchar AS
$$
DECLARE
test CURSOR IS SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
oidfinal varchar;
BEGIN
oid=0;
for oid in test loop
COPY (SELECT pg_get_indexdef(oid)) TO '/home/xxxxx/test2.copy';
end loop;
return oidfinal;
end;
$$
LANGUAGE plpgsql;

Vous n'aurez plus alors à vous embêter avec l'échappement des cotes.

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

Merci rjuju,

  J'avai mis de côté cette problématique la semaine derniére, j'y reviens aujourd'hui:

CREATE OR REPLACE FUNCTION recup_index()
RETURNS void
AS $BODY$
DECLARE
test CURSOR FOR SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
BEGIN
oid := 0;
for oid in test loop
COPY (SELECT pg_get_indexdef(oid)) TO '\xxxxxxxx\test2.copy';
end loop;
return;
end;
$BODY$
LANGUAGE plpgsql;

   Suis je bien sur la bonne piste ? j'ai toujours une erreur:

opsnetv3=> \i recupindex.sql
psql:recupindex.sql:15: ERROR:  syntax error at or near "$1"
LINE 1:   $1
          ^
QUERY:    $1
CONTEXT:  SQL statement in PL/PgSQL function "recup_index" near line 6

Merci encore.

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

J'ai du mal à comprendre, votre script est syntaxiquement correct. Etes-vous sur de lancer le bon script ?

Sinon, il faut faire le copy de cette manière :

EXECUTE 'COPY (SELECT pg_get_indexdef(' || oid || ')) TO ''\xxxxxxxx\test2.copy''';

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

CREATE OR REPLACE FUNCTION recup_index()
RETURNS void
AS $BODY$
DECLARE
test CURSOR FOR SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
oid int;
BEGIN
oid := 0;
for oid in test loop
EXECUTE 'COPY (SELECT pg_get_indexdef(oid)) TO 'xxxxxxxxxxxx/testindex.sql'';
end loop;
return;
end;
$BODY$
LANGUAGE plpgsql;

  Je me suis aperçu que la fonction COPY ne fonctionne qu'avec le user admin POSTGRE. J'ai donc dut faire cette commande aprés m'être connécté en tant que user postgres pour pouvoir ne serait ce que lancer ma création de procédure:

CREATE LANGUAGE plpgsql;    sinon il ne reconnait pas, néanmoins j'obtiens la même erreur aprés ces essais a savoir:

psql:recupindex.sql:15: ERROR:  syntax error at or near "$1"
LINE 1:   $1
          ^
QUERY:    $1
CONTEXT:  SQL statement in PL/PgSQL function "recup_index" near line 6

A la ligne 6 l'on a: la declaration du curseur qui fonctionne en dehors de la procedure et la déclaration du int OID; rien de bien suspect.

Rjuju vous avez mis des barres de concatenation pour le paramétre oid passé a la fonction pg_get_indexdef ainsi que des guillemets, est ce bien utile  ? il s'agit ici d'une valeur int. J'ai tésté également: même erreur. Si vous avez d'autres idées ^^

Dernière modification par d_light (29/10/2012 16:58:34)

rjuju

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

Quelle version de postgres utilisez-vous ? oid est un mot réservé, vous devriez essayer un autre nom de variable.

---

Julien.
https://rjuju.github.io/

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

CREATE OR REPLACE FUNCTION recup_index()
RETURNS void
AS $BODY$
DECLARE
test CURSOR FOR SELECT attrelid from pg_attribute where attrelid in (select oid from pg_class where relname in (select indexname from pg_indexes));
recup int;
BEGIN
recup := 0;
for recup in test loop
EXECUTE 'COPY (SELECT pg_get_indexdef(recup)) TO 'xxxxxxxxxx/testindex.sql'';
end loop;
return;
end;
$BODY$
LANGUAGE plpgsql;

Même erreur, je suis en 8.2.6

Merci Rjuju

frost242

Administrateur

Re : Récupération sous forme d'export table et data sans indexs

Comme rjuju vous l'a déjà indiqué, votre procédure stockée ne va pas fonctionner correctement. L'ordre COPY est appelé pour chaque ligne retournée par le curseur, et à chaque itération va écraser le fichier créé à l'itération précédente. Vous n'aurez alors que la définition du dernier index retourné par votre requête.
L'ordre suivant est un peu plus simple que ce que vous faites et vous récupère l'ordre de création de tous les index utilisateurs. La fonction et son curseur sont inutiles d'ailleurs:

COPY (SELECT pg_get_indexdef(indexrelid) FROM pg_stat_user_indexes) TO '..../testindex.sql'

---

Thomas Reiss

d_light

Membre

Re : Récupération sous forme d'export table et data sans indexs

Merci a vous Frost et Rjuju, en effet c'est beaucoup plus efficace et surtout cela fonctionne.

Dans le même ordre d'idée j'essai de récupérer les ordres de créations des contraintes via pg_get_constraintdef en vain pour le momment, je n'obtiens que des noms de contraintes sans les ordres de création et j'ai des erreurs du type ERROR:  could not find tuple for constraint 4855035

Je pense que je ne vais pas pouvoir récupérer les ordres via cette fonction, ces contraintes doivent être dans la définition de la table j'imagine plutôt qu'a part. Encore faut t'il que je fasse le tri. L'idée étant d'importer sans contraintes ni index pour pouvoir les reconstruire derriere.