如何在 PostgreSQL 中的函数内返回 SELECT 的结果？

使用 RETURN QUERY：

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- also visible as OUT param in function body
               , cnt   bigint
               , ratio bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- column alias only visible in this query
        , (count(*) * 100) / _max_tokens  -- I added parentheses
   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 参数上使用列别名。不过，这在其他情况下可能会模棱两可。有多种方法可以避免任何混淆：

使用 SELECT 列表中项目的序号位置：ORDER BY 2 DESC。示例：选择每个 GROUP BY 组中的第一行？重复表达式 ORDER BY count(*)。 （此处不需要。）设置配置参数plpgsql.variable_conflict或使用特殊命令#variable_conflict error |使用变量 |在函数中使用_column。请参阅：函数参数与使用 USING 子句的 JOIN 结果之间的命名冲突

不要使用“文本”或“计数”作为列名。在 Postgres 中使用两者都是合法的，但“count”是标准 SQL 中的 reserved word 和基本函数名，“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 () 是一个 window function。您可以使用 CTE 代替子查询。很漂亮，但是在像这样的简单情况下（主要是在 Postgres 12 之前），子查询通常更便宜。

使用 OUT 参数或 RETURNS TABLE（隐式使用 OUT 参数）时的最终 explicit RETURN statement is not required（但允许）。

round() with two parameters 仅适用于 numeric 类型。子查询中的 count() 产生 bigint 结果，而在此 bigint 上的 sum() 产生 numeric 结果，因此我们自动处理 numeric 数字，一切就位。

---

您好，请查看以下链接

https://www.postgresql.org/docs/current/xfunc-sql.html

前任：

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

---