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

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

呼叫：

SELECT * FROM word_frequency(123);

说明：

>显式定义返回类型比简单地声明为记录更加实用。这样，您不必为每个函数调用提供列定义列表。 RETURNS TABLE是一种方法。还有其他。 OUT参数的数据类型必须完全匹配查询返回的内容。
>仔细选择OUT参数的名称。它们几乎在任何地方都在函数体中可见。表限定同名的列，以避免冲突或意外结果。我在我的例子中的所有列。

但请注意OUT参数cnt和同名的列别名之间的潜在命名冲突。在这种特殊情况下(RETURN QUERY SELECT …)Postgres以任何一种方式使用OUT参数上的列别名。这在其他上下文中可能是模糊的。有多种方法可以避免任何混淆：

>使用SELECT列表中项的顺序位置：ORDER BY 2 DESC。例：

> Select first row in each GROUP BY group?

>重复表达式ORDER BY count(*)。
>(不适用于此处。)设置配置参数plpgsql.variable_conflict或使用特殊命令#variable_conflict error | use_variable | use_column每个函数。例：

> Naming conflict between function parameter and result of JOIN with USING clause

>不要使用“文本”和“计数”作为列名称。两者都是合法的在Postgres中使用，但“计数”是标准SQL中的一个reserved word和一个基本的函数名称，“文本”是一个基本的数据类型。可能导致混乱的错误。我在我的例子中使用txt和cnt。
>添加了一个缺失;并校正头中的语法错误。 (_max_tokens int)，not(int maxTokens) – 类型后的名称。
>在使用整数除法时，最好先乘以后再除法，以最小化舍入误差。更好：使用数字(或浮点类型)。见下文。

替代

这是我认为你的查询应该实际上(计算每个令牌的相对份额)：

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

表达式sum(t.cnt)OVER()是一个window function.你可以使用一个CTE而不是子查询 – 漂亮，但是一个子查询通常在这种简单的情况下更便宜。

最后explicit RETURN statement is not required(但允许)使用OUT参数或RETURNS TABLE(隐式使用OUT参数)。

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