Comment renvoyer le résultat d’un SELECT dans une fonction dans PostgreSQL?

Utilisez RETURN QUERY :

 CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int) RETURNS TABLE ( txt text -- visible as OUT parameter inside and outside function , cnt bigint , ratio bigint) AS $func$ BEGIN RETURN QUERY SELECT t.txt , count(*) AS cnt -- column alias only visible inside , (count(*) * 100) / _max_tokens -- I added brackets FROM ( SELECT t.txt FROM token t WHERE t.chartype = 'ALPHABETIC' LIMIT _max_tokens ) t GROUP BY t.txt ORDER BY cnt DESC; -- note the potential ambiguity END $func$ LANGUAGE plpgsql; 

Appel:

 SELECT * FROM word_frequency(123); 

Explication:

• Il est beaucoup plus pratique de définir explicitement le type de retour que de le déclarer simplement en tant qu’enregistrement. De cette façon, vous n’avez pas à fournir de liste de définitions de colonnes pour chaque appel de fonction. RETURNS TABLE est un moyen d’y parvenir. Il y en a d’autres Les types de données des parameters OUT doivent correspondre exactement à ce qui est renvoyé par la requête.

• Choisissez avec soin les noms pour les parameters OUT . Ils sont visibles dans le corps de la fonction presque partout. Colonnes de qualification de table du même nom pour éviter les conflits ou les résultats inattendus. Je l’ai fait pour toutes les colonnes de mon exemple.

  Mais notez le conflit de noms potentiel entre le paramètre OUT cnt et l’alias de colonne du même nom. Dans ce cas particulier ( RETURN QUERY SELECT ... ) Postgres utilise l’alias de colonne sur le paramètre OUT toute façon. Cela peut être ambigu dans d’autres contextes. Il existe différentes manières d’éviter toute confusion:

  1. Utilisez la position ordinale de l’élément dans la liste SELECT: ORDER BY 2 DESC . Exemple:
     • Sélectionnez la première ligne de chaque groupe GROUP BY?
  2. Répétez l’expression ORDER BY count(*) .
  3. (Non applicable ici.) Définissez le paramètre de configuration plpgsql.variable_conflict ou utilisez la commande spéciale #variable_conflict error | use_variable | use_column #variable_conflict error | use_variable | use_column #variable_conflict error | use_variable | use_column par fonction. Exemple:
     • Conflit de dénomination entre le paramètre de fonction et le résultat de la clause JOIN avec USING

• N’utilisez pas “text” et “count” comme noms de colonnes. Les deux sont légaux à utiliser dans Postgres, mais “count” est un mot réservé en SQL standard et un nom de fonction de base et “text” est un type de données de base. Peut conduire à des erreurs déroutantes. J’utilise txt et cnt dans mes exemples.

• Ajout d’un manquant ; et corrigé une erreur de syntaxe dans l’en-tête. (_max_tokens int) , pas (int maxTokens) – tapez après le nom .

• En travaillant avec une division entière, il est préférable de multiplier d’abord et de diviser plus tard pour minimiser l’erreur d’arrondi. Encore mieux: travaillez avec un type numeric (ou un type à virgule flottante). Voir ci-dessous.

Alternative

C’est ce que je pense que votre requête devrait ressembler (calcul d’un partage relatif par jeton ):

 CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int) RETURNS TABLE ( txt text , abs_cnt bigint , relative_share numeric) AS $func$ BEGIN RETURN QUERY SELECT t.txt , t.cnt , round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2) -- AS relative_share FROM ( SELECT t.txt , count(*) AS cnt FROM token t WHERE t.chartype = 'ALPHABETIC' GROUP BY t.txt ORDER BY cnt DESC LIMIT _max_tokens ) t ORDER BY t.cnt DESC; END $func$ LANGUAGE plpgsql; 

L’expression sum(t.cnt) OVER () est une fonction de fenêtre . Vous pourriez utiliser un CTE au lieu de la sous-requête – jolie, mais une sous-requête est généralement moins chère dans des cas simples comme celui-ci.

Une instruction RETURN explicite finale n’est pas requirejse (mais autorisée) lorsque vous utilisez les parameters OUT ou RETURNS TABLE (qui utilise implicitement les parameters OUT ).

round() avec deux parameters ne fonctionne que pour numeric types numeric . count() dans la sous-requête produit un résultat bigint et une sum() sur cette bigint produit un résultat numeric . Nous traitons donc automatiquement un nombre numeric et tout se met en place.