如何在PostgreSQL中的函数内返回SELECT的结果? [英] How to return result of a SELECT inside a function in PostgreSQL?

问题描述

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

I have this function in PostgreSQL, but I don't know how to return the result of the query:

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函数中返回查询结果.

But I don't know how to return the result of the query inside the PostgreSQL function.

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

I found that the return type should be SETOF RECORD, right? But the return command is not right.

正确的方法是什么?

推荐答案

使用 致电:

SELECT * FROM word_frequency(123);

说明:

• 显式定义返回类型比简单地将其声明为记录要实际得多.这样，您不必在每次函数调用时都提供列定义列表. RETURNS TABLE 是一种方法那.还有其他OUT参数的数据类型必须与查询返回的内容完全匹配.

• It is much more practical to explicitly define the return type than simply declaring it as record. This way you don't have to provide a column definition list with every function call. RETURNS TABLE is one way to do that. There are others. Data types of OUT parameters have to match exactly what is returned by the query.

仔细选择OUT参数的名称.它们在功能主体中几乎任何位置都可见.对具有相同名称的列进行表限定，以避免冲突或意外结果.我对示例中的所有列都执行了此操作.

Choose names for OUT parameters carefully. They are visible in the function body almost anywhere. Table-qualify columns of the same name to avoid conflicts or unexpected results. I did that for all columns in my example.

但是请注意，OUT参数cnt与同名列别名之间可能存在命名冲突.在这种特殊情况下(RETURN QUERY SELECT ...)，Postgres会使用OUT参数上的列别名.但是，在其他情况下，这可能是模棱两可的.有多种避免混淆的方法:

But note the potential naming conflict between the OUT parameter cnt and the column alias of the same name. In this particular case (RETURN QUERY SELECT ...) Postgres uses the column alias over the OUT parameter either way. This can be ambiguous in other contexts, though. There are various ways to avoid any confusion:

1. 使用该项目在SELECT列表中的顺序位置:ORDER BY 2 DESC.例子:
   • 在每个GROUP BY组中选择第一行?

1. Use the ordinal position of the item in the SELECT list: ORDER BY 2 DESC. Example:
   • Select first row in each GROUP BY group?

• 函数参数和使用USING子句的JOIN结果之间的命名冲突

请勿使用文本"或计数"作为列名.两者在Postgres中都是合法使用的，但是"count"是 标准SQL中的保留字 和基本函数名，文本"是基本数据类型.可能导致混乱的错误.我在示例中使用txt和cnt.

添加了丢失的;，并更正了标题中的语法错误. (_max_tokens int)，而不是(int maxTokens)- type ，在 name 之后.

Added a missing ; and corrected a syntax error in the header. (_max_tokens int), not (int maxTokens) - type after name.

在使用整数除法时，最好先相乘然后再相除，以最大程度地减少舍入误差.甚至更好:使用numeric(或浮点类型).见下文.

While working with integer division, it's better to multiply first and divide later, to minimize the rounding error. Even better: work with numeric (or a floating point type). See below.

这是我认为的查询的实际外观(计算每个令牌的相对份额)():

This is what I think your query should actually look like (calculating a relative share per token):

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 代替子查询-漂亮，但是在像这样的简单情况下，子查询通常更便宜.

The expression sum(t.cnt) OVER () is a window function. You could use a CTE instead of the subquery - pretty, but a subquery is typically cheaper in simple cases like this one.

最后的显式当使用OUT参数或RETURNS TABLE(隐式使用OUT参数)时，RETURN语句不是 必需(但允许).

A final explicit RETURN statement is not required (but allowed) when working with OUT parameters or RETURNS TABLE (which makes implicit use of OUT parameters).

带有两个参数的round() 仅适用于 numeric 类型.子查询中的count()产生bigint结果，而在该bigint上的sum()产生numeric结果，因此我们自动处理numeric数字，一切都就位了.

round() with two parameters only works for numeric types. count() in the subquery produces a bigint result and a sum() over this bigint produces a numeric result, thus we deal with a numeric number automatically and everything just falls into place.

这篇关于如何在PostgreSQL中的函数内返回SELECT的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！