Forums PostgreSQL.fr

bebert73

Membre

utililisation d'un type "tableau" dans pl/pgsql

Bonjour

En-dehors de la documentation, y-a-t-il des bouquins ou des sites que vous pouvez conseiller pour mieux maîtriser le pl/pgsql ? La doc est bien faite, mais un peu légère en exemples je trouve du côté pl...

Je cherche à faire la chose suivante, je soupçonne que ce n'est pas bien compliqué mais je ne trouve pas la syntaxe.

Soit une table articles :

CREATE TABLE articles (
   id       INTEGER PRIMARY KEY,
   valide BOOLEAN NOT NULL DEFAULT FALSE
);

Soit une table avec les désignations dans plusieurs langues
CREATE TABLE designations (
   id_article       INTEGER REFERENCES articles(id),
   langue           CHAR(3) REFERENCES langues(langue)
   designation    VARCHAR(255) NOT NULL,
   CONSTRAINT designations_pk PRIMARY KEY (id_article, langue)
)

Vous l'aurez compris, il s'agit tout simplement d'une table d'articles et d'une table de leurs désignations en plusieurs langues.

Je veux faire en sorte que l'utilisateur lambda n'a jamais accès à aucune table directement (aucun GRANT), il n'y accède que via des fonctions en SECURITY DEFINER.

J'ai donc fait deux fonctions :
- get_articles_valides() en SECURITY DEFINER, qui renvoie un SETOF d'INTEGERS (en fait les "id" des articles dont "valide" est à TRUE)

Son code est très simple, c'est un "SELECT id FROM articles WHERE valide = TRUE"

- get_designation(IN article_in INTEGER) en SECURITY INVOKER, qui renvoie la désignation de l'article dans la langue préférée du USER (cette langue est stockée dans une autre table, et pour récupérer la désignation il passe par une autre fonction en SECURITY DEFINER, vu qu'il n'a pas accès directement à la table des désignations...je ne détaille pas ces fonctions et tables ici, ça n'a pas d'intérêt pour mon problème)

Je souhaite ensuite faire une fonction qui renvoie un tableau (id_articles, designation) de tous les articles valides.

La désignation devant être dans la langue du user, cette fonction sera forcément SECURITY INVOKER.

Normalement avec les deux fonctions ci-dessus, get_articles_valides() et get_designation(article_in), j'ai tout ce qu'il faut.

Donc ma fonction (qui ne marche pas) :

CREATE FUNCTION get_designations_valides () RETURNS TABLE (id_article_out INTEGER, designation_out VARCHAR) AS $$
DECLARE
   v_id INTEGER;
   v_tableau TABLE (id_article INTEGER, designation VARCHAR);
BEGIN
   FOR v_id IN SELECT article_id FROM get_articles_valides() LOOP
          SELECT v_id, get_designation(v_id) INTO v_tableau;
   END LOOP;
RETURN v_tableau;
END;
$$ LANGUAGE PLPGSQL SECURITY INVOKER

Voilà, je pense que ce que je veux faire est clair avec ce code, par contre bien sur ce code ne marche pas, apparemment ce n'est pas la bonne syntaxe pour travailler avec des tableaux dans pl/pgsql.

Dernière modification par bebert73 (09/06/2011 11:32:49)

Marc Cousin

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

Une fonction qui retourne des listes d'enregistrement (TABLE par exemple) doit retourner ses enregistrements un par un avec la syntaxe return next.

Il y a un exemple ici : http://docs.postgresql.fr/9.0/plpgsql-c … tures.html , chapitre 3.6.2

Par contre, ce n'est à mon avis pas une bonne idée de faire ça avec une fonction. Une vue serait bien plus appropriée, cela sera entre autres plus performant: avec votre façon d'écrire, vous forcez la méthode d'exécution de la requête. Il est probable que le moteur aurait été bien plus performant si on lui avait laissé davantage de liberté.

---

Marc.

bebert73

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

ok c'est noté je vais passer par une vue

mais par curiosité j'aimerais quand même savoir ce qui cloche dans ma fonction

Pour le NEXT, j'avais déjà lu cette page et j'ai fait des essais, mais ce n'était pas concluant non plus...notamment je n'arrive pas à savoir quoi mettre dans la partie DECLARE de ma fonction. Ma situation est un peu différente de celle de l'exemple, car le SELECT ne se fait pas sur une table, mais sur le résultat d'une fonction, donc je ne peux pas mettre de %ROWTYPE dans la partie DECLARE

Ci-dessous le code que j'ai tenté, apparemment il n'aime pas le type "TABLE" dans la partie DECLARE

CREATE FUNCTION get_designations_valides () RETURNS TABLE (id_article_out INTEGER, designation_out VARCHAR) AS $$
DECLARE
t_designation    TABLE (v_id INTEGER, v_designation VARCHAR)
BEGIN
    FOR t_designation IN SELECT id, get_designation(id) FROM get_articles_valides() LOOP
        RETURN NEXT t_designation;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL SECURITY INVOKER;

Dernière modification par bebert73 (09/06/2011 12:49:17)

bebert73

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

à noter que j'ai essayé aussi avec un type "SETOF",  pareil il ne l'accepte pas

Marc Cousin

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

non, le pb c'est le type de t_designation. Et vous ne pourrez pas déclarer un type composite à cet endroit là.

Il faut d'abord faire un create type en SQL, puis utiliser ce type pour t_designation.

---

Marc.

bebert73

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

ok si je crée le type t_designation ça marche en effet, je pensais qu'on pouvait créer temporairement un type au sein d'une fonction si on n'en a pas besoin ailleurs

Une vue serait bien plus appropriée, cela sera entre autres plus performant: avec votre façon d'écrire, vous forcez la méthode d'exécution de la requête. Il est probable que le moteur aurait été bien plus performant si on lui avait laissé davantage de liberté.

une question concernant la façon dont PG gère les vues : j'ai bien compris que pour ramener l'ensemble des articles valides et leur désignation dans la bonne langue, une vue est bien plus performante qu'une fonction qui boucle sur tous les articles

Mais supposons que je veuille ramener la designation d'un seul article.

J'ai deux possibilités :
- soit faire un SELECT sur la vue que je viens de créer (par exemple SELECT designation FROM v_mes_articles WHERE id_article = 212)

- soit utiliser la fonction get_designation (IN article_in INTEGER), cette fonction faisant directement un SELECT dans la table des designations (par exemple SELECT designation FROM designations WHERE id_article = 212 AND langue = get_langue(USER))

Quelle est la meilleure solution ? Pour ramener une seule ligne j'opterais pour la 2ème (la fonction) car je suppose que si on utilise la vue, postgresql doit d'abord la construire avant de ramener la ligne souhaitée (donc construire la vue de toutes les désignations de tous les articles valides dans la langue du user), non ?.

A moins que postgresql n'arrive à optimiser la construction de sa vue en fonction du SELECT (c'est à dire à ne construire qu'une vue partielle, avec uniquement la ligne qui nous intéresse). Il  sait faire ça ?

Marc Cousin

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

Oui, les prédicats de la clause WHERE sont poussés dans la requête de la vue.

---

Marc.

bebert73

Membre

Re : utililisation d'un type "tableau" dans pl/pgsql

ok, donc la fonction est complètement inutile dans ce cas, il faut privilégier la vue même si on veut ramener une seule ligne

ça marche

merci !