您的位置: 首页  >  技术问答  >  在单个参数中传递多个值

在单个参数中传递多个值

分类: 技术问答 • 2022-04-29 14:40:33
问题描述：

假设我具有此功能:

CREATE OR REPLACE FUNCTION test_function(character varaying)
  RETURNS integer AS
$BODY$
DECLARE
some_integer integer;
begin
   Select column2 from test_table where column1 in ($1) into some_integer;
end;
Return some_integer;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

我想这样称呼它:

Select * from test_function ('data1', 'data2','data3');

当然，不能用这种方法来完成，因为Postgres试图用该名称和三个不存在的参数来查找函数.

Of course, it cannot be done this way, because Postgres tries to find function with this name and three parameter which doesn't exists.

我试图用引号引起来，但在这种情况下，参数解释错误: data1'，'data2'，'data3 ，就像一个字符串.

I tried to put quotes around commas but in that case parameter is interpreted wrong: data1', 'data2','data3, like one string.

有没有一种方法可以在参数中放置多个值，以便IN子句可以识别它?

Is there a way to put multiple values in parameter so IN clause can recognized it?

答

不会创建您的函数. end之后的RETURN是语法上的废话.

Your function wouldn't be created. RETURN after end is syntactical nonsense.

无论哪种方式，具有

Either way, a function with a VARIADIC parameter does exactly what you ask for:

CREATE OR REPLACE FUNCTION test_function(VARIADIC varchar[])
 RETURNS SETOF integer AS
$func$
SELECT column2
FROM   test_table
WHERE  column1 = ANY($1);
$func$  LANGUAGE sql;

致电(根据需要):

SELECT * FROM test_function('data1', 'data2', 'data3');

使用简单的SQL函数，简单示例不需要plpgsql.但是VARIADIC也适用于plpgsql函数.

Using a simple SQL function, plpgsql is not required for the simple example. But VARIADIC works for plpgsql functions, too.

使用RETURNS SETOF integer，因为显然可以返回多行.

Using RETURNS SETOF integer since this can obviously return multiple rows.

详细信息:

  • Pass multiple values in single parameter
  • Return rows matching elements of input array in plpgsql function
  • VARIADIC parameter must be the last input parameter
  • Return rows matching elements of input array in plpgsql function

SQL Fiddle 演示，带有附加参数.

SQL Fiddle demo with additional parameters.

相关推荐