在 plpgsql 函数中返回匹配输入数组元素的行 [英] Return rows matching elements of input array in plpgsql function

问题描述

我想创建一个执行以下操作的 PostgreSQL 函数:

I would like to create a PostgreSQL function that does something like the following:

CREATE FUNCTION avg_purchases( IN last_names text[] DEFAULT '{}' )
  RETURNS TABLE(last_name text[], avg_purchase_size double precision)
AS
$BODY$
DECLARE
  qry text;
BEGIN
qry := 'SELECT last_name, AVG(purchase_size) 
          FROM purchases
          WHERE last_name = ANY($1)
          GROUP BY last_name'
RETURN QUERY EXECUTE qry USING last_names;
END;
$BODY$

但我在这里看到两个问题:

But I see two problems here:

1. 我不清楚数组类型是最有用的输入类型.

2. 当我这样做时，当前返回零行:

1. It is not clear to me that array type is the most useful type of input.

2. This is currently returning zero rows when I do:

SELECT avg_purchases($${'Brown','Smith','Jones'}$$);

我错过了什么?

推荐答案

这有效:

CREATE OR REPLACE FUNCTION avg_purchases(last_names text[] = '{}')
  RETURNS TABLE(last_name text, avg_purchase_size float8) AS
$func$
   SELECT last_name, AVG(purchase_size)::float8
   FROM   purchases
   WHERE  last_name = ANY($1)
   GROUP  BY last_name
$func$  LANGUAGE sql;

调用:

SELECT * FROM avg_purchases('{foo,Bar,baz,"}weird_name''$$"}');

或(更新 - 示例与 美元引用):

Or (update - example with dollar-quoting):

SELECT * FROM avg_purchases($x${foo,Bar,baz,"}weird_name'$$"}$x$);

• 更多关于如何引用字符串文字:
  在 PostgreSQL 中插入带单引号的文本

  这里不需要动态 SQL.

  You don't need dynamic SQL here.

  虽然您可以将它包装成一个 plpgsql 函数(这可能很有用)，但一个简单的 SQL 函数就可以很好地完成这项工作.

  While you can wrap it into a plpgsql function (which may be useful), a simple SQL function is doing the job just fine.

  您有类型不匹配.

  • avg() 的结果可能是 numeric 以保存精确的结果.我强制转换为 float8 以使其工作，这只是 double precision 的别名(您可以使用任何一个).如果您需要完美的精度，请改用 numeric.
  • 因为你 GROUP BY last_name 你想要一个普通的 text OUT 参数而不是 text[].
  • the result of avg() may be numeric to hold a precise result. I cast to float8 to make it work, which is just an alias for double precision (you can use either). If you need perfect precision, use numeric instead.
  • Since you GROUP BY last_name you want a plain text OUT parameter instead of text[].

  数组是一种有用的输入类型.如果您的客户更容易，您还可以使用 VARIADIC 允许将数组作为元素列表传递的输入参数:

  An array is a useful type of input. If it's easier for your client you can also use a VARIADIC input parameter that allows to pass the array as a list of elements:

  CREATE OR REPLACE FUNCTION avg_purchases(VARIADIC last_names text[] = '{}')
    RETURNS TABLE(last_name text, avg_purchase_size float8) AS
  $func$
     SELECT last_name, AVG(purchase_size)::float8
     FROM   purchases
     JOIN  (SELECT unnest($1)) t(last_name) USING (last_name)
     GROUP  BY last_name
  $func$  LANGUAGE sql;
  

  调用:

  SELECT * FROM avg_purchases('foo', 'Bar', 'baz', '"}weird_name''$$"}');
  

  或(用美元引用):

  SELECT * FROM avg_purchases('foo', 'Bar', 'baz', $y$'"}weird_name'$$"}$y$);
  

  请注意，标准 Postgres 只允许最多 100 个元素.这是在编译时由 预设选项确定的:

  Be aware that standard Postgres only allows a maximum of 100 elements. This is determined at compile time by the preset option:

  max_function_args(整数)

  报告函数参数的最大数量.由构建服务器时FUNC_MAX_ARGS的值决定.默认值为 100 个参数.

  Reports the maximum number of function arguments. It is determined by the value of FUNC_MAX_ARGS when building the server. The default value is 100 arguments.

  当以关键字VARIADIC为前缀时，您仍然可以使用数组符号调用它:

  You can still call it with array notation when prefixed with the keyword VARIADIC:

  SELECT * FROM avg_purchases(VARIADIC '{1,2,3, ... 99,100,101}');
  

  对于更大的数组(100+)，我也会使用 unnest() 在子查询中并 JOIN 到它，这往往会更好地扩展:

  For bigger arrays (100+), I would also use unnest() in a subquery and JOIN to it, which tends to scale better:

  • 使用大 IN 优化 Postgres 查询

  这篇关于在 plpgsql 函数中返回匹配输入数组元素的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！