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

Question

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

166

我在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，对吗？但是返回命令不正确。

有什么正确的方法吗？

- Renato Dinhani

为什么要计算它们？你的token表中有重复的token吗？另外，请在你的问题中添加表定义。 - wildplasser

1

这是你的整个函数吗？如果函数中没有其他语句，你应该将其设置为 LANGUAGE SQL。 - jpmc26

2个回答

8

请参阅以下链接的文档： https://www.postgresql.org/docs/current/xfunc-sql.html 示例：

    CREATE FUNCTION sum_n_product_with_tab (x int)
    RETURNS TABLE(sum int, product int) AS $$
        SELECT $1 + tab.y, $1 * tab.y FROM tab;
    $$ LANGUAGE SQL;

- Moumita Das

是的，尽可能使用“纯SQL”更好。您可以使用两个或多个命令（SELECT、INSERT等），只有最后一个是返回值。对于过程性“依赖逐步”的解决方法是在WITH子句中使用一系列子句。例如：WITH t1 as (SELECT etc1), t2 as (SELECT etc2 from t1) SELECT result FROM t2; - Peter Krauss

@PeterKrauss 可以问一下为什么吗？有没有任何参考资料推荐 SQL 而不是 PLPGSQL？ - Rafs

嗨 @Rafs，是的...现在很难找到确切的PostgreSQL功能，但是JIT优化器可以例如在SQL-VIEW中重用SQL代码，https://www.postgresql.org/docs/current/jit-reason.html - Peter Krauss

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

使用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和相同名称的列别名之间可能存在名称冲突(naming conflict)。在这种特定情况下(RETURN QUERY SELECT ...)，Postgres无论如何都会使用列别名覆盖OUT参数。不过，在其他情况下，这可能会产生歧义。有各种方法可以避免任何混淆：

使用SELECT列表中项的序数位置：ORDER BY 2 DESC。例如：
- Select first row in each GROUP BY group?
重复表达式ORDER BY count(*)。
(在此处不需要.)设置配置参数plpgsql.variable_conflict或在函数中使用特殊命令#variable_conflict error|use_variable|use_column。请参见：
- Naming conflict between function parameter and result of JOIN with USING clause

不要使用“text”或“count”作为列名称。Postgres都允许使用这两个名称，但是“count”是标准SQL的一个保留字(reserved word)和一个基本函数名称，“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语句（但是允许使用）。 round()带有两个参数的round()函数仅适用于numeric类型。子查询中的count()产生一个bigint结果，而对这个bigint执行sum()会生成一个numeric结果，因此我们自动处理了一个numeric数字，一切顺理成章。