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?

I think you can pass an array parameter. Have a look at this: stackoverflow.com/questions/570393/… — mlinth
– mlinth, Commented Jan 23, 2015 at 11:40

Erwin Brandstetter · Accepted Answer · 2024-06-12 16:53:22Z

1

(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:

edited Jun 12, 2024 at 16:53

answered Jan 23, 2015 at 17:46

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aco Vujic Over a year ago

but what if i have more parameters? let's say (date, date, VARIADIC varchar[])... how do I call this function?

Aco Vujic Over a year ago

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

Erwin Brandstetter Over a year ago

@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.

Collectives™ on Stack Overflow

Passing multiple values in single parameter

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related