Discussion :

[ginger4957]

Bonjour

Je cherche à afficher le commentaire d'une séquence postgres, ainsi que le commentaire d'une fonction, et ce soit à l'aide de requêtes sql, soit à l'aide de psql sous linux…

J'ai essayé directement sous la console ceci :
\d nom_sequence

mais ça me renvoie ceci et non le commentaire en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Séquence « public.act_id »
    Colonne    |  Type   
---------------+---------
 sequence_name | name
 last_value    | bigint
 increment_by  | bigint
 max_value     | bigint
 min_value     | bigint
 cache_value   | bigint
 log_cnt       | bigint
 is_cycled     | boolean
 is_called     | boolean

Auriez vous une idée de comment faire pour récupérer le commentaire d'une séquence et aussi d'une fonction sous postgres, comme on récupère le commentaire d'une table ou de ses champs ?

Pour une table je fais comme ça et ça marche :

Code :

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

1
2
3
4
5
6
7
8
9
 
-- Premièrement je récupère l'oid correspondant à ma table
select oid from pg_class where relname = 'nom_table';
 
-- Puis je récupère le commentaire
select description from pg_description where objoid = '123456789' and objsubid = 0;
 
-- On peut aussi récupérer le commentaire comme ça :
select col_description('123456789','0');

Cordialement,
Ginger

[estofilo]

Avec psql, \ds+ devrait faire l'affaire pour une séquence ou \df+ pour une fonction.
En SQL, on peut aussi aller chercher dans pg_description comme pour une table, mais avec l'OID de la séquence.

[ginger4957]

Merci pour la réponse Pour le commentaire d'une séquence ça fonctionne bien en psql :

Code :

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

1
2
 
\ds+ nom_sequence

Ainsi que par ces 2 solutions de requête sql :

Code :

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

1
2
select col_description('321654987','0');
select description from pg_description where objoid = '321654987' and objsubid = 0;

Pour le commentaire d'une fonction ça marche aussi avec psql, par contre en sql je n'arrive pas à récupérer l'oid de la fonction, ça existe au moins ?

J'ai essayé ces 3 solutions :

Code :

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

1
2
3
select oid from pg_class where relname = 'ma_fonction(param1, param2, param3)';
select oid from pg_class where relname = 'ma_fonction()';
select oid from pg_class where relname = 'ma_fonction';

Mais le champ oid reste vide peut-être que c'est normal ?

[estofilo]

Dans le cas d'une fonction l'OID doit être récupéré dans pg_catalog.pg_proc (plutôt que pg_class).

[ginger4957]

J'ai essayé ça :

Code :

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

1
2
 
SELECT 'ma_fonction(character varying,bigint,numeric)'::regprocedure::oid;

Et ça renvoie bien l'oid de la fonction
Je récupère la liste des fonctions comme ça :

Code :

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

1
2
 
select * from pg_proc proc join pg_language lang on proc.prolang = lang.oid where lang.lanname = 'plpgsql';

et donc pour ta solution (merci ) j'ai tenté ceci :

Code :

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

1
2
 
select oid from pg_catalog.pg_proc where proname = 'calcul_montant_remise' and proargnames = '{param1,param2,param3}' order by proname

Mais si il y a plusieurs fonctions avec le même nom, comment savoir quel résultat est le bon ? Est ce possible de comparer les résultats avec le champ proargtypes ? C'est ce qui me semble être la seule solution de comparaison en ce cas…

Si oui, est ce que chaque type (chaine, entier, decimal etc…) possède un numéro bien spécifique ? Parce que si j'affiche les résultats de cette requête, soit l'oid, le nom, les arguments et les types de la fonction :

Code :

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

1
2
 
select oid, proname, proargnames, proargtypes from pg_catalog.pg_proc where proname = 'ma_fonction'

Résultats (2 lignes de résultats que j'ai condensé ici pour plus de clarté):

Code :

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

1
2
3
4
5
 
oid : 123456789 | 321654987
proname : ma_fonction | ma_fonction
proargnames : {character varying,bigint,numeric} | {character varying,double precision,numeric}
proargtypes : 1043 20 1700 | 1043 701 1700

Et bien 1043 correspondrait à character varying, 20 à bigint, 1700 à numeric et double precision à 701…
Est ce que c'est toujours pareil pour tout le monde ?

Parce que si oui, comment écrire cette requête correctement ? :

Code :

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

1
2
 
select 'ma_fonction(1043,20,1700)'::regprocedure::oid;

Ou alors comment récupérer les bons types des paramètres de la fonction ma_fonction ?

En attendant j'utilise ma solution (qui au final fait bien la différence entre les fonctions grâce aux types des parametres…)

[estofilo]

Et bien 1043 correspondrait à character varying, 20 à bigint, 1700 à numeric et double precision à 701…
Est ce que c'est toujours pareil pour tout le monde ?

Oui ce sont les OIDs des types dans pg_type.

[ginger4957]

Ah ok

J'ai ces deux requêtes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-- récupère tous les noms des types
select typname from pg_type 
 
-- Récupère toutes les fonctions et leurs types de paramètres
SELECT proname, proargtypes, proargnames 
FROM pg_proc proc 
JOIN pg_language lang ON proc.prolang = lang.oid 
WHERE lang.lanname = 'plpgsql';
 
-- la 2e requete renvoie :
nom_fonction1, 1043 20 1700, {param1,param2,param3}
nom_fonction2, 1043 701 1700, {param1,param2,param3}
…

En fait il faudrait que je puisse retrouver les typname correspondant à chaque proargtypes de chaque fonction trouvée, donc j'ai essayé de faire une jointure entre les tables pg_type et pg_proc :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
-- récupère toutes les fonctions et leurs types (numeros et noms)
SELECT proname, proargtypes, proargnames, typname 
FROM pg_proc proc 
JOIN pg_type ON proargtypes[0] = pg_type.oid
JOIN pg_language lang ON proc.prolang = lang.oid 
WHERE lang.lanname = 'plpgsql';
 
-- Ce qui renvoie :
nom_fonction1, 1043 20 1700, {param1,param2,param3}, varchar
nom_fonction2, 1043 701 1700, {param1,param2,param3}, varchar

Suivons par exemple nom_fonction1 :

Code :

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

1
2
3
 
-- Si je met proargtypes[1] ça renvoie int8
-- Si je met proargtypes[2] ça renvoie numeric

Probleme résolu merci beaucoup !

[estofilo]

Sinon tu peux faire la même chose que psql c'est-à-dire utiliser la fonction pg_catalog.pg_get_function_arguments(). Elle prend l'oid de la fonction et elle renvoie la liste en texte des arguments de la fonction avec leurs types.

[ginger4957]

Bonjour

J'essaie de faire la même chose que ce que j'avais fait, soit ceci :

SELECT proname, proargtypes, proargnames, typname
FROM pg_proc proc
JOIN pg_type ON proargtypes[0] = pg_type.oid
JOIN pg_language lang ON proc.prolang = lang.oid
WHERE lang.lanname = 'plpgsql';

-- Ce qui renvoie :
nom_fonction1, 1043 20 1700, {param1,param2,param3}, varchar
nom_fonction2, 1043 701 1700, {param1,param2,param3}, varchar

… Mais il faudrait que ça me renvoie ça :

Code :

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

1
2
 
nom_fonction1, 1043 20 1700, {param1,param2,param3}, varchar bigint numeric

Je ne sais pas comment faire ma requête pour que ça trouve le nom de chaque type correspondant au numéro de chaque type…
Plus explicitement :
1043 20 1700 CORRESPOND A varchar bigint numeric

(et donc pour l'instant j'arrive seulement à récupérer varchar…)

En attendant j'essaie d'utiliser ta solution pg_catalog.pg_get_function_arguments() merci

EDIT


Si je fais ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
-- essai 1
SELECT pg_catalog.pg_get_function_arguments(p.oid) 
FROM pg_catalog.pg_proc p 
 
-- essai 2
SELECT pg_catalog.pg_get_function_arguments(p.oid) 
FROM pg_catalog.pg_proc p 
JOIN pg_language lang ON p.prolang = lang.oid 
WHERE lang.lanname = 'plpgsql'

Ca me renvoie une erreur :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
RROR:  function pg_catalog.pg_get_function_arguments(oid) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
 
 
********** Erreur **********
 
ERROR: function pg_catalog.pg_get_function_arguments(oid) does not exist
État SQL :42883
Astuce : No function matches the given name and argument types. You may need to add explicit type casts.

… ?

[estofilo]

C'est quelle version de postgresql?

[ginger4957]

Je suis sous pg_admin version 1.14 et pour postgres j'ai la 8.0.15

[estofilo]

Non il faut faire

Code :

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

select version();

pour avoir la version de postgresql
Les objets et fonctions de pg_catalog dépendent pas mal de la version de postgresql.

[ginger4957]

Oui je viens de voir ça j'ai mis la version dans le post précédent du coup.

[ginger4957]

Ah je pense que j'ai trouvé une alternative :

Code :

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

1
2
3
4
5
6
7
 
SELECT typname 
FROM pg_proc proc 
JOIN pg_type ON proargtypes[0] = pg_type.oid OR proargtypes[1] = pg_type.oid OR proargtypes[2] = pg_type.oid OR proargtypes[3] = pg_type.oid OR proargtypes[4] = pg_type.oid 
JOIN pg_language lang ON proc.prolang = lang.oid 
WHERE lang.lanname = 'plpgsql' 
AND proargtypes = '1043 701 1700 20 20';

[ginger4957]

Bonjour

Le code que j'ai trouvé hier fonctionne bien, mais quand il y a une centaine de requêtes à faire ça met un temps fou (j'execute mes requêtes dans une boucle php, je sais ce n'est pas bien :s) :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
// données parametres
$hote = $_POST['hote'];
$port = $_POST['port'];
$bdd = $_POST['bdd'];
$util = $_POST['util'];
$mdp = $_POST['mdp'];
$proj = $_POST['proj'];

// connexion à la base
$connect = pg_connect( "host=".$hote." port=".$port." dbname=".$bdd." user=".$util." password=".$mdp );

// je recupere les fonctions et leurs arguments
$reqArgs = 'SELECT proname, proargnames 
FROM pg_proc proc 
JOIN pg_language lang ON proc.prolang = lang.oid 
WHERE lang.lanname = \'plpgsql\';';
$resArgs = pg_query($connect,$reqArgs);

$fonctions[] = array(); // tableau des fonctions
$tabArgs = array(); // tableau des arguments
while($rowArgs = pg_fetch_array($resArgs))
{
        $fonctions[] = $rowArgs[0];	
       $tabArgs[] = $rowArgs[1];
}

for( $i=0; $i<count($tabArgs); $i++ )
{
        $arg = substr( $tabArgs[$i],1,strlen($tabArgs[$i]) );
	$arg = substr( $arg,0,(strlen($arg)-1) );
	
	// on compte le nb d'arguments
	$nbArg = substr_count($arg,',')+1;
	
	// on execute la requete
	$reqComFct = 'SELECT proname, typname 
	FROM pg_proc proc';
	
	for( $j=0; $j<$nbArg; $j++)
	{
		if($j == 0)
		{
			$reqComFct .= ' JOIN pg_type ON proargtypes['.$j.'] = pg_type.oid';
		}
		if($j > 0)
		{
			$reqComFct .= ' OR proargtypes['.$j.'] = pg_type.oid';
		}
	}
	
	$reqComFct .= ' JOIN pg_language lang ON proc.prolang = lang.oid 
	WHERE lang.lanname = \'plpgsql\' 
	ORDER BY proname;';
	echo $reqComFct; // si j'affiche toutes les requetes, aucun pb elles s'affichent correctement
	
	$resComFct = pg_query($connect,$resComFct);
	echo $resComFct; // par contre évidemment dès que je veux executer toutes mes requetes, ça prend beaucoup trop de temps au risque de faire planter le navigateur...
}

Est ce que c'est possible de faire autrement ? :s

[estofilo]

Si au final le but est similaire à la commande \df de psql (liste de toutes les fonctions avec leurs arguments) tu peux lancer psql avec l'option -E, faire \df et voir quelle requête il fait lui (c'est à ça que sert l'option -E).

[ginger4957]

Merci c'est parfait

J'ai testé \df et \df+, voici ce la requête sql pour cette dernière commande :

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
19
 
SELECT n.nspname as "Schéma",
  p.proname as "Nom",
  CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||
  pg_catalog.format_type(p.prorettype, NULL) as "Type de données du résultat",
  pg_catalog.oidvectortypes(p.proargtypes) as "Type de données des paramètres",
  u.usename as "Propriétaire",
  l.lanname as "Langage",
  p.prosrc as "Code source",
  pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND NOT p.proisagg
      AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 3, 4;

Elle renvoie toutes les fonctions et leurs commentaires, nikel.

je ne comprend pas le ORDER BY 1, 2, 3, 4 par contre :s A quoi correspondent les 1 2 3 et 4 ? EDIT --> Ah j'ai compris, il s'agit de chaque colonne dans le select c'est bien ça ?

Je vais essayer de la modifier pour que ça renvoie seulement les fonctions du schéma, et non les fonctions postgres du catalogue (pg_catalog).

EDIT

Voici la requête qui récupère toutes les fonctions et leurs commentaires :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT p.proname as "Nom", pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid) 
AND n.nspname = 'public'
ORDER BY 1;

Un grand merci pour tes réponses ça m'a beaucoup aidé !

[estofilo]

je ne comprend pas le ORDER BY 1, 2, 3, 4 par contre :s A quoi correspondent les 1 2 3 et 4 ? EDIT --> Ah j'ai compris, il s'agit de chaque colonne dans le select c'est bien ça ?

Oui c'est un raccourci qui veut dire ordonne par la 1ere colonne, puis par la 2eme etc...
Ca évite de recopier toute l'expression dans le ORDER BY

[ginger4957]

Ok ! C'est bien pratique pour ordonner.

Encore merci !