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. Note that the first array element has index 1.

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

1. Call:

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

Output:

2.  Call:

CALL views.p1('title', 10)

Output:

title

3. Call:

CALL views.p1('zzz')

Output:

zzz

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. Note that the first array element has index 1.

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

1. Call:

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

Output:

2.  Call:

CALL views.p1('title', 10)

Output:

title

3. Call:

CALL views.p1('zzz')

Output:

zzz