Forums PostgreSQL.fr

zied

Membre

Procédure stockée et un tableau en tant que paramètre

Est-il possible de passer un tableau de dimension variable en paramètre à une procédure stockée plpgsql?

exemple :

 create function myfnc (in param1 character, in param2 int,...., in param array)
....

Si oui, est-il possible de faire des itérations pour chaque élément du tableau pour exécuter des requêtes?

Dernière modification par zied (13/11/2009 10:23:31)

Marc Cousin

Membre

Re : Procédure stockée et un tableau en tant que paramètre

Oui, on peut passer un tableau de quelque chose (entier, chaine de caractère, enregistrement de table, type composé déclaré, etc…)
Voila les éléments de syntaxe :
http://docs.postgresql.fr/8.4/arrays.html
Ensuite il y a des fonctions sur tableau (pour par exemple connaitre leur taille et boucler dessus) :
http://docs.postgresql.fr/8.4/functions-array.html
Au passage, on peut faire des fonctions polymorphiques (avec des déclarations anyarray par exemple) :
http://docs.postgresql.fr/8.4/extend-ty … olymorphic

---

Marc.

zied

Membre

Re : Procédure stockée et un tableau en tant que paramètre

Merci pour ton aide.
Je mets l'exemple de la procédure que j'ai faite. ça pourrait servir pour certains. De plus, si vous avez des conseils pour améliorer ce code, je suis preneur.

CREATE OR REPLACE FUNCTION __defresprev(IN idres bigint, IN rolesid bigint[], OUT result boolean)
  RETURNS boolean AS
$BODY$

declare 
 tab bigint[]; 
 di int2; 
 m bigint;
 i int2; 

BEGIN
result:=false;

DELETE FROM com_previleges
 WHERE id_res=$1;

 tab:=$2;
di:=(SELECT array_upper(tab, 1));

FOR i IN 1..di LOOP

	m:=tab[i];
	INSERT INTO com_previleges(allow, id_res, role_id)
		VALUES (true, $1, m);

END LOOP;

result:=true;

End;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION __defresprev(bigint, bigint[]) OWNER TO postgres;

Dernière modification par zied (16/11/2009 00:20:46)

gleu

Administrateur

Re : Procédure stockée et un tableau en tant que paramètre

Voici ce à quoi j'arrive (avec une 8.4 obligatoirement à cause de la fonction unnest) :

base1=# CREATE OR REPLACE FUNCTION __defresprev2(IN p_idres bigint, IN p_rolesid bigint[], OUT p_result boolean)
  RETURNS boolean AS
$BODY$
BEGIN
p_result:=false;
DELETE FROM com_previleges WHERE id_res=p_idres;
INSERT INTO com_previleges(allow, id_res, role_id)
  SELECT true, p_idres, roleid FROM unnest(p_rolesid) as roleid;
p_result:=true;
END;
$BODY$
LANGUAGE 'plpgsql'
VOLATILE
COST 100;

Ma fonction est plus rapide car elle fait appel justement à unnest plutôt que d'utiliser une boucle FOR généralement lente en PL/pgsql. Voici un exemple :

base1=# \timing
Chronométrage activé.
base1=# select __defresprev(2, array[3, 1, 2, 4]);
 __defresprev
--------------
 t
(1 ligne)

Temps : 1,308 ms
base1=# select __defresprev2(2, array[3, 1, 2, 4]);
 __defresprev2
---------------
 t
(1 ligne)

Temps : 1,123 ms
base1=# select __defresprev(2, array[3, 1, 2, 4, 6, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]);
 __defresprev
--------------
 t
(1 ligne)

Temps : 2,079 ms
base1=# select __defresprev2(2, array[3, 1, 2, 4, 6, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]);
 __defresprev2
---------------
 t
(1 ligne)

Temps : 1,154 ms

Petite question supplémentaire, par curiosité : à quoi vous sert la variable result? de toute façon, en cas de problème, la fonction ne renverra rien... donc je ne comprends pas à quoi sert cette variable.

---

Guillaume.

zied

Membre

Re : Procédure stockée et un tableau en tant que paramètre

Petite question supplémentaire, par curiosité : à quoi vous sert la variable result? de toute façon, en cas de problème, la fonction ne renverra rien... donc je ne comprends pas à quoi sert cette variable.

Bonne réflexion, je pense que  c'est juste à cause d'un copier coller que j'ai fait au tout début et par la suite ça m'a échappé!

Sinon, malheureusement, je suis en version 8.3 donc je n'ai pas de fonction unnest!

Merci Gleu

Marc Cousin

Membre

Re : Procédure stockée et un tableau en tant que paramètre

Bon, puisqu'on en est aux objections…

Le unnest est plus rapide, effectivement, aucun doute là dessus.

Mais je pense que l'endroit où on peut le plus gagner (pas forcément en temps d'exécution unitaire, mais en évitant du bloat inutile), c'est en évitant de supprimer des enregistrements de com_previleges qu'on va réinsérer dans la boucle d'après. L'algo devrait donc supprimer les enregistrements de com_previleges qui ont allow à false (il n'y a pas de nul pour cette colonne je présume). Ensuite, on peut déterminer les opérations minimalistes à faire avec cette requête

SELECT com_privileges.roleid AS deja_presents, param_roleid AS a_rajouter 
FROM com_previleges 
FULL OUTER JOIN unnest(p_rolesid) AS param_roleid 
    ON (com_privileges.roleid=param_roleid) 
WHERE com_privileges.roleid IS NULL
  OR param_roleid IS NULL;

Dernière modification par Marc Cousin (16/11/2009 10:22:54)

---

Marc.