如何高效地从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中文网其他相关文章！