Discussion :

[D_light]

Bonjour,

J'aimerai mettre au point une fonction ou procédure PL récupérant les ordres de création d'objet, ici par exemple les indexs dans un fichier. Je viens de l'univers oracle et il semblerai que les petites différences entre les 2 types de PL me pose probléme.

Voici la fonction qui récupére les oid dans un curseur qui les passe ensuite dans une boucle for a la fonction getindexddl() pour ensuite récupérer le texte dans un fichier pour chaque occurence, mais ça ne fonctionne pas:

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
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;
 COPY oidfinal TO '/home/xxxx/test2.copy';
end loop;
return oidfinal;
end;'
LANGUAGE plpgsql;

J'obtiens a a chaque fois des erreurs différentes au fur à mesure que je progresse jamais explicite la derniere en date:

ERROR: syntax error at or near "/"
LINE 11: COPY oidfinal TO '/home/xxxxx/test2.copy'...

Merci pour votre aide si vous voyez des incohérences ou que vous avez des suggestions.

[estofilo]

L'erreur de syntaxe est dûe à l'utilisation des apostrophes pour encadrer le code de la fonction qui clashent avec les apostrophes à l'intérieur de la fonction.

Aujourd'hui pour éviter ça on utilise le "dollar-quoting" pour les déclarations de fonction, c.a.d

Code :

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

1
2
3
4
5
CREATE FUNCTION nomfunction etc...
AS 
$BODY$
  .. corps de la fonction
$BODY$ language plpgsql;

$BODY$ peut être remplacé par n'importe quelle chaîne tant que ça commençe par un dollar et que ça finit par un dollar. Voir la doc pour plus de détails.

[D_light]

Merci Estofilo pour cette petite astuce.

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
 
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;

Cela ne fonctionne toujours pas, suis je au moins sur la bonne piste pour faire ce que je désire ?

Voici l'erreur obtenu:
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

[estofilo]

Je ne vois pas trop l'intérêt des sous-requêtes avec pg_attribute et pg_class.
Si le but est de sortir en fichier la définition de tous les index, la ligne seule suivante suffit:

Code :

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

COPY (select pg_get_indexdef(indexname::regclass) from pg_indexes) TO '/tmp/index.sql';

[D_light]

Il faut bien que je récupére les valeurs d'indexname, a priori la fonction pg_getindexdef ne prend que comme paramétre l'oid de l'index. D'ou l'idée de récupérer les oid dans un curseur ?

[estofilo]

Il faut voir que pg_get_indexdef(indexname::regclass) est équivalent à pg_get_indexdef(oid-de-l'index)

sauf que ça évite la jointure avec pg_class.

La jointure avec pg_attribute va sortir une ligne par colonne de l'index et n'est pas nécessaire et même génante dès qu'il y a plusieurs colonnes par index puisque ça va sortir la définition de l'index en doublon.

[D_light]

Bien vu !

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
CREATE OR REPLACE FUNCTION recup_index()
RETURNS void
AS $BODY$
DECLARE
test CURSOR FOR SELECT 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 '/xxxxxxxxx/testindex.sql'';
end loop;
return;
end;
$BODY$
LANGUAGE plpgsql;

Merci a vous, par contre je n'arrive toujours pas a comprendre l'erreur obtenue.

[D_light]

C'est bon c'est réglé, merci.

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.