VARIADIC Procedure Parameters

Instead of using just an IN parameter, the last non-optional parameter can be declared VARIADIC to indicate that it can be repeated 0 or more times when the procedure is called positionally.

Here's an example procedure:

CREATE virtual procedure views.p1 (title string, VARIADIC data integer) RETURNS (v string) AS
BEGIN
	DECLARE integer i = 1;
	CREATE local temporary table #t (v string);
	INSERT INTO #t select title;
	INSERT INTO #t select array_length(data);
	WHILE (i <= array_length(data))
	BEGIN
		INSERT INTO #t SELECT data[i];
		i = i + 1;
	END
	SELECT * FROM #t;
END;

This example procedure illustrates that the VARIADIC parameter may be used as a regular array: indexing with [] (line 9) and array_length() (lines 6 and 7) function perfectly work with it. Please note that the first array element has index 1.

Here are several sample results that are generated by the procedure.

The call

CALL views.p1('aaa', 10, 20, 33)

gives the following output result set:

The call

CALL views.p1('title', 10)

gives the following output result set:

title

The call

CALL views.p1('zzz')

gives the following output result set:

zzz