如何有效率地從PostgreSQL函數傳回查詢結果？

PostgreSQL 函數中傳回查詢結果

PostgreSQL 提供兩種從函數傳回查詢結果的方法：OUT 參數和 RETURNS TABLE。使用 OUT 參數時，函數簽章指定接收查詢值的變數清單。但是，這種方法禁止明確傳回類型定義，並可能導致列名衝突。

使用 RETURNS TABLE

為了獲得更方便和強大的解決方案，請考慮使用 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中文網其他相關文章！