PostgreSQL 関数からクエリ結果を効率的に返すにはどうすればよいですか?

PostgreSQL 関数でクエリ結果を返す

PostgreSQL には、関数からクエリ結果を返すための 2 つのメソッド (OUT パラメータと RETURNS TABLE) が用意されています。 OUT パラメーターを使用する場合、関数シグネチャはクエリ値を受け取る変数のリストを指定します。ただし、このアプローチでは明示的な戻り値の型の定義が禁止されており、列名の競合が発生する可能性があります。

返品テーブルを使用

より便利で強力なソリューションとして、RETURNS TABLE の使用を検討してください。この構文は関数の正確な戻りパターンを定義し、明示的な列名と型の定義を可能にします。

<code class="language-sql">CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- 在函数体中也可见为 OUT 参数
               , cnt   bigint
               , ratio bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- 列别名仅在此查询中可见
        , (count(*) * 100) / _max_tokens  -- 我添加了括号
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;                    -- 潜在的歧义
END
$func$;

SELECT * FROM word_frequency(123);</code>

注:

• 競合を避けるために、テーブル修飾子を使用して OUT パラメータの列名を修飾します。
• 曖昧さを避けるために、OUT パラメーターと列の別名を慎重に選択してください。
• 列名として「text」または「count」を使用することは避けてください。
• より正確な整数の除算を行うには、数値データ型の使用を検討してください。

ウィンドウ関数を使用する代替手段

各トークンの相対的なシェアを計算する必要がある場合は、クエリでウィンドウ関数の使用を検討してください。

<code class="language-sql">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$;</code>

このクエリは、over() 句を使用して各トークンの相対的なシェアを計算します。

以上がPostgreSQL 関数からクエリ結果を効率的に返すにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。