为了账号安全,请及时绑定邮箱和手机立即绑定

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

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

扬帆大鱼 2019-11-27 14:13:56
我在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 DESCEND$$LANGUAGE plpgsql;但是我不知道如何在PostgreSQL函数中返回查询结果。我发现返回类型应该为SETOF RECORD,对吗?但是return命令不正确。什么是正确的方法?
查看完整描述

2 回答

?
千巷猫影

TA贡献1829条经验 获得超7个赞

用途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自动编号和一切都只是属于地方。


查看完整回答
反对 回复 2019-11-27
  • 2 回答
  • 0 关注
  • 1714 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信