返回行匹配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''$$"}');

或（更新 - 例如使用<一个href=\"http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\"相对=nofollow>美元符）：

Or (update - example with dollar-quoting):

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

• 更多关于如何报价字符串：结果
  <一href=\"http://stackoverflow.com/questions/12316953/insert-varchar-with-single-quotes-in-postgresql/12320729#12320729\">Insert VARCHAR与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.

  您的类型不匹配

  
  
  • 的平均结果（）可数字持有precise结果。我投以的float8 ，使其工作，这仅仅是双precision 别名（可以使用其一）。如果你需要完美的precision，使用数字代替。
  
  
  • 既然你 GROUP BY姓氏你想要一个简单的文本 OUT参数，而不是正文[] 。
  
  
  • 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[].

  数组被输入的一个有用的类型。如果您的客户端更容易，你也可以使用<一个href=\"http://www.postgresql.org/docs/current/interactive/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS\"相对=nofollow> 可变参数 输入参数，允许传递数组作为元素的列表：

  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个元素的。这是在编译时由 preSET确定选项​​：

  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.

  您仍然可以使用数组方式调用它时$ P $用关键字可变参数 pfixed：

  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（） 子查询和加入给它，这往往能够更好地扩展

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

  
  
  • 优化用大一个Postgres查询在
  
  
  • Optimizing a Postgres query with a large IN

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