Vous n'êtes pas identifié(e).
Pages : 1
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)
Hors ligne
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.
Hors ligne
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)
Hors ligne
à noter que j'ai essayé aussi avec un type "SETOF", pareil il ne l'accepte pas
Hors ligne
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.
Hors ligne
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 ?
Hors ligne
Oui, les prédicats de la clause WHERE sont poussés dans la requête de la vue.
Marc.
Hors ligne
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 !
Hors ligne
Pages : 1
