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

Question

我在PostgreSQL中有此功能，但是我不知道如何返回查询结果：

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)

  RETURNS SETOF RECORD AS

$$

BEGIN

    SELECT text, count(*), 100 / maxTokens * count(*)

    FROM (

        SELECT text

    FROM token

    WHERE chartype = 'ALPHABETIC'

    LIMIT maxTokens

    ) as tokens

    GROUP BY text

    ORDER BY count DESC

END

$$

LANGUAGE plpgsql;

但是我不知道如何在PostgreSQL函数中返回查询结果。

我发现返回类型应该为SETOF RECORD，对吗？但是return命令不正确。

什么是正确的方法？

Answer 1

用途RETURN QUERY：CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)  RETURNS TABLE (txt   text   -- also visible as OUT parameter inside 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;                    -- potential ambiguity END$func$  LANGUAGE plpgsql;呼叫：SELECT * FROM word_frequency(123);说明：这是很多更实用的明确定义的返回类型不是简单地声明为记录。这样，您不必在每次函数调用时都提供列定义列表。RETURNS TABLE是做到这一点的一种方法。还有其他 OUT参数的数据类型必须与查询返回的内容完全匹配。OUT仔细选择参数名称。它们在功能主体中几乎任何位置都可见。对具有相同名称的列进行表限定，以避免冲突或意外结果。我在示例中对所有列进行了此操作。但是请注意，参数和同名列别名之间可能存在命名冲突。在这种特殊情况下（），Postgres会以两种方式在参数上使用列别名。但是，在其他情况下，这可能是模棱两可的。有多种避免混淆的方法：OUTcntRETURN QUERY SELECT ...OUT使用项目在SELECT列表中的顺序位置：ORDER BY 2 DESC。例：在每个GROUP BY组中选择第一行？重复表达式ORDER BY count(*)。（此处不适用。）设置配置参数plpgsql.variable_conflict或#variable_conflict error | use_variable | use_column在功能中使用特殊命令。看到：使用USING子句命名函数参数和JOIN结果之间的冲突请勿使用“文本”或“计数”作为列名。两者在Postgres中都是合法使用的，但是“ count”是标准SQL中的保留字，并且是基本函数名称，而“ 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) 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 ()是一个窗口函数。您可以使用CTE代替子查询-漂亮，但是在这种简单情况下，子查询通常更便宜。最后明确RETURN声明不要求有工作的时候（但允许）OUT参数或RETURNS TABLE（使隐式使用的OUT参数）。round()具有两个参数的参数仅适用于numeric类型。count()子查询产生bigint的结果和sum()在此bigint产生numeric的结果，因此，我们对付numeric自动编号和一切都只是属于地方。