SQL 如何在 PostgreSQL 中的函数内返回 SELECT 的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7945932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return result of a SELECT inside a function in PostgreSQL?
提问by Renato Dinhani
I have this function in PostgreSQL, but I don't know how to return the result of the query:
我在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;
But I don't know how to return the result of the query inside the PostgreSQL function.
但是我不知道如何在 PostgreSQL 函数内部返回查询的结果。
I found that the return type should be SETOF RECORD, right? But the return command is not right.
我发现返回类型应该是SETOF RECORD，对吧？但是返回命令不对。
What is the right way to do this?
这样做的正确方法是什么？
回答by Erwin Brandstetter
Use RETURN QUERY:
使用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;
Call:
称呼：
SELECT * FROM word_frequency(123);
Explanation:
解释：
It is muchmore 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 TABLEis one way to do that. There are others. Data types ofOUTparameters have to match exactly what is returned by the query.Choose names for
OUTparameters 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.But note the potential naming conflictbetween the
OUTparametercntand the column alias of the same name. In this particular case (RETURN QUERY SELECT ...) Postgres uses the column alias over theOUTparameter either way. This can be ambiguous in other contexts, though. There are various ways to avoid any confusion:- Use the ordinal position of the item in the SELECT list:
ORDER BY 2 DESC. Example: - Repeat the expression
ORDER BY count(*). - (Not applicable here.) Set the configuration parameter
plpgsql.variable_conflictor use the special command#variable_conflict error | use_variable | use_columnin the function. See:
- Use the ordinal position of the item in the SELECT list:
Don't use "text" or "count" as column names. Both are legal to use in Postgres, but "count" is a reserved wordin standard SQL and a basic function name and "text" is a basic data type. Can lead to confusing errors. I use
txtandcntin my examples.Added a missing
;and corrected a syntax error in the header.(_max_tokens int), not(int maxTokens)- typeafter name.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.
这是很多更实用的明确定义的返回类型不是简单地声明为记录。这样您就不必为每个函数调用提供一个列定义列表。
RETURNS TABLE是做到这一点的一种方法。还有其他人。OUT参数的数据类型必须与查询返回的内容完全匹配。OUT仔细选择参数的名称。它们几乎在函数体中的任何地方都可见。表限定同名列以避免冲突或意外结果。我对示例中的所有列都这样做了。但请注意参数和同名列别名之间的潜在命名冲突。在这种特殊情况下 ( ) Postgres 使用列别名而不是参数。但是，这在其他上下文中可能不明确。有多种方法可以避免混淆：
OUTcntRETURN QUERY SELECT ...OUT- 使用项目在 SELECT 列表中的顺序位置：
ORDER BY 2 DESC。例子： - 重复表达式
ORDER BY count(*)。 - （此处不适用。） 设置配置参数
plpgsql.variable_conflict或使用#variable_conflict error | use_variable | use_column函数中的特殊命令。看：
- 使用项目在 SELECT 列表中的顺序位置：
不要使用“text”或“count”作为列名。两者在 Postgres 中使用都是合法的，但“count”是标准 SQL 中的保留字和基本函数名，而“text”是基本数据类型。可能会导致令人困惑的错误。我在我的例子中使用
txt和cnt。;在标题中添加了缺失并更正了语法错误。(_max_tokens int)不(int maxTokens)-键入后的名称。在使用整数除法时，最好先乘后除，以最小化舍入误差。更好的是：使用
numeric（或浮点类型）。见下文。
Alternative
选择
This is what I thinkyour 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;
The expression sum(t.cnt) OVER ()is a window function. You coulduse a CTEinstead of the subquery - pretty, but a subquery is typically cheaper in simple cases like this one.
表达式sum(t.cnt) OVER ()是一个窗函数。您可以使用CTE而不是子查询 - 很漂亮，但在像这样的简单情况下，子查询通常更便宜。
A final explicit RETURNstatement is notrequired(but allowed) when working with OUTparameters or RETURNS TABLE(which makes implicit use of OUTparameters).
最后明确RETURN声明不要求有工作的时候（但允许）OUT参数或RETURNS TABLE（使隐式使用的OUT参数）。
round()with two parametersonly works for numerictypes. count()in the subquery produces a bigintresult and a sum()over this bigintproduces a numericresult, thus we deal with a numericnumber automatically and everything just falls into place.
round()带有两个参数仅适用于numeric类型。count()在子查询中产生一个bigint结果，a sum()over thisbigint产生一个numeric结果，因此我们numeric自动处理一个数字，一切都恰到好处。
回答by Moumita Das
Hi please check the below link
您好，请查看以下链接
https://www.postgresql.org/docs/current/xfunc-sql.html
https://www.postgresql.org/docs/current/xfunc-sql.html
EX:
前任：
CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
SELECT + tab.y, * tab.y FROM tab;
$$ LANGUAGE SQL;
