Search code examples
sqlpostgresqlparameter-passingplpgsql

Passing multiple values in single parameter

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?

Solution

  • (Your function wouldn't be created. RETURN after END is syntactical nonsense.)

    A function with a VARIADIC parameter does exactly what you ask for:

    CREATE OR REPLACE FUNCTION test_function(date, date, VARIADIC varchar[])
  RETURNS SETOF integer
  LANGUAGE sql AS
$func$
SELECT col1
FROM   test_table
WHERE  col3 > $1
AND    col4 < $2
AND    col2 = ANY($3)
$func$;

    fiddle - demo with additional parameters
    Old sqlfiddle

    Call (as desired):

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

    Using LANGUAGE sql, PL/pgSQL is not required for the simple example. But VARIADIC works for either.

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

    See: