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

小开

最佳答案

使用 < a href = “ https://www.postgreql.org/docs/current/plpgsql-control-structures.html # PLPGSQL-STATEMENT-RETURING”rel = “ noReferrer”> 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。示例:

在每个 GROUPBY 组中选择第一行？

重复表达式 ORDER BY count(*)。

(这里不需要)设置配置参数 plpgsql.variable_conflict或使用函数中的特殊命令 #variable_conflict error | use_variable | use_column。参见:

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

不要使用“文本”或“计数”作为列名。两者在 Postgres 都是合法使用的，但“ count”是标准 SQL 中的保留意见，而“ text”是基本函数名，“ 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 ()是窗口功能。可以使用慢性创伤性脑病而不是子查询。漂亮，但是子查询通常在像这样的简单情况下更便宜(大多数在 Postgres 12之前)。

在使用 OUT参数或 RETURNS TABLE(隐式使用 OUT参数)时，最终的显式的 RETURN语句是 < em > not 必需的(但是允许)。

带有两个参数的 round()仅适用于 < a href = “ https://www.postgreql.org/docs/current/datatype-numic.html # DATATYPE-NUMERIC-DECIMAL”rel = “ noReferrer”> numeric 类型。子查询中的 count()产生一个 bigint结果，而这个 bigint上的 sum()产生一个 numeric结果，因此我们自动处理一个 numeric数字，一切都就绪了。