1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Aco Vujic
  • 67
  • 3
  • 11
  • 1
    I think you can pass an array parameter. Have a look at this: http://stackoverflow.com/questions/570393/postgres-integer-arrays-as-parameters – mlinth Jan 23 '15 at 11:40

1 Answers1

0

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

db<>fiddle here - demo with additional parameters
Old sqlfiddle

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • but what if i have more parameters? let's say (date, date, VARIADIC varchar[])... how do I call this function? – Aco Vujic Jan 26 '15 at 08:57
  • when I call it like this: SELECT * FROM test_function('2015-01-01','2015-01-20','data1', 'data2','data3'); I get error - function test_function(date, date, unknown, unknown, unknown) does not exist – Aco Vujic Jan 26 '15 at 09:26
  • @AcoVujic: You can only have *one* `VARIADIC` parameter and it must be the last one. Please read the manual, I added a link. Your call would work. I added a fiddle. – Erwin Brandstetter Jan 26 '15 at 11:42