在plpgsql函数中返回匹配输入数组元素的行

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

我想创建一个PostgreSQL函数，它的功能如下:

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. It is not clear to me that array type is the most useful type of input.
2. 我不清楚数组类型是最有用的输入类型。

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

   这是当前返回的零行，当我这样做时:

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

What am I missing?

我缺少什么?

1 个解决方案

#1

This works:

如此:

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;

Call:

电话:

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$);

• More about how to quote string literals:
  Insert text with single quotes in PostgreSQL

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

• You don't need dynamic SQL here.

  这里不需要动态SQL。

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

  虽然可以将其封装到plpgsql函数中(这可能很有用)，但是一个简单的SQL函数可以很好地完成这项工作。

• You have type mismatches.

  类型不匹配。

  • 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.
  • avg()的结果可以是数值的，以获得精确的结果。我将其转换为float8，以使其工作，这只是双精度的别名(您也可以使用)。如果你需要完美的精度，用数字代替。
  • Since you GROUP BY last_name you want a plain text OUT parameter instead of text[].
  • 由于使用last_name进行分组，所以需要一个纯文本输出参数，而不是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

Call:

电话:

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

Or (with dollar-quoting):

或(dollar-quoting):

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

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

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

max_function_args (integer)

max_function_args(整数)

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.

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

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

当以关键字VARIADIC:

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

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

对于更大的数组(100+)，我还将在子查询中使用unnest()，并加入到它中，这样可以更好地扩展:

• Optimizing a Postgres query with a large IN
• 使用较大的IN对Postgres查询进行优化