sql - Passing multiple values in single parameter

Question

Let's say I have this function:

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;

And I want to call it like this:

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

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

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

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

Answer 1

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;

Call (as desired):

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

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

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

Details:

• 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 demo with additional parameters.