[解決済み] PostgreSQLの関数内でSELECTの結果を返すには？

質問

PostgreSQLでこの関数を持っていますが、クエリの結果をどのように返せばよいのかわかりません。

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
  RETURNS SETOF RECORD AS
$$
BEGIN
    SELECT text, count(*), 100 / maxTokens * count(*)
    FROM (
        SELECT text
    FROM token
    WHERE chartype = 'ALPHABETIC'
    LIMIT maxTokens
    ) as tokens
    GROUP BY text
    ORDER BY count DESC
END
$$
LANGUAGE plpgsql;

しかし、PostgreSQL関数の内部でクエリの結果を返す方法がわかりません。

私は、戻り値の型が SETOF RECORD でなければならないことがわかりました。しかし、returnコマンドは正しくありません。

正しい方法は何でしょうか？

どのように解決するのですか？

使用方法 RETURN QUERY :

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- also visible as OUT parameter inside function
               , cnt   bigint
               , ratio bigint)
  LANGUAGE plpgsql 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;                    -- potential ambiguity 
END
$func$;

呼び出す。

SELECT * FROM word_frequency(123);

戻り値の型を明示的に定義することは 多く を返すよりもずっと実用的です。 record . この方法では、関数を呼び出すたびに列の定義リストを提供する必要がありません。 RETURNS TABLE はそのための一つの方法です。他にもあります。のデータ型は OUT パラメータのデータ型は、クエリによって返されるものと正確に一致しなければなりません。

の名前を選択します。 OUT の名前を慎重に選んでください。それらは関数本体のほとんどどこでも見ることができます。同じ名前のカラムをテーブル修飾して、衝突や予期せぬ結果を避ける。私の例では、すべてのカラムについてそうしました。

しかし、潜在的な ネーミングの衝突 の間にある OUT パラメータ cnt と同名のカラムエイリアスを指定します。この特別な場合 ( RETURN QUERY SELECT ... ) Postgres はカラムのエイリアスを OUT パラメータを使用します。しかし、これは他の文脈では曖昧になる可能性があります。混乱を避けるために、様々な方法があります。

1. SELECTリスト内の項目の序列位置を使用する。 ORDER BY 2 DESC . 例
   • 各GROUP BYグループの最初の行を選択しますか？
2. 式を繰り返す ORDER BY count(*) .
3. (ここでは適用されません。) 設定パラメータ plpgsql.variable_conflict を設定するか、特殊コマンド #variable_conflict error | use_variable | use_column を関数の中で使うことができます。参照してください。
   • 関数のパラメータとUSING句を使ったJOINの結果との名前の衝突

列名として "text" または "count" を使用しないでください。どちらもPostgresでは合法的に使用できますが、"count"は 予約語 は標準SQLで基本的な関数名、"text"は基本的なデータ型です。紛らわしいエラーになる可能性があります。私が使っている txt と cnt のように、より明示的な名前が必要かもしれません。

欠けていた ; を追加し、ヘッダのシンタックスエラーを修正しました。 (_max_tokens int) ではなく (int maxTokens) - タイプ 後 名前 .

整数の割り算の作業では、丸め誤差を少なくするために、先に掛け算をして、後で割り算をするのがよいでしょう。あるいは numeric または浮動小数点型にする。以下を参照してください。

代替

これは、私が を考えています。 のようなクエリになるはずです。 トークンごとの相対シェア ):

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt            text
               , abs_cnt        bigint
               , relative_share numeric)
  LANGUAGE plpgsql 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$;

式は sum(t.cnt) OVER () は ウィンドウ関数 . あなたは は を使用します。 CTE をサブクエリの代わりに使用します。きれいですが、今回のような単純なケースではサブクエリの方が一般的に安くなります（主にPostgres 12以前）。

最終的に 明示的な RETURN ステートメントは ではなく 必須 (を使用する場合（ただし許可される）。 OUT パラメータや RETURNS TABLE (を暗黙のうちに使用する）。 OUT パラメータを暗黙的に使用します）。

round() の2つのパラメータを持つ のみが動作します。 numeric の型があります。 count() をサブクエリで指定すると bigint の結果と sum() の上に、この bigint を生成します。 numeric の結果が得られるので、ここでは numeric を自動的に処理し、すべてがうまくいくのです。