VARIADIC parameter must be the last input parameter

Question

How to create two VARIADIC parameters. Look at my code and correct me.

CREATE OR REPLACE FUNCTION ip_source_test(text,text,date,date,VARIADIC int[],VARIADIC text[])
RETURNS TABLE (no_documents int, "Report By" text, "Report_By" text) AS 
$$
BEGIN
IF 'Source Member' = $1 THEN
RETURN QUERY SELECT.....
ELSEIF 'company' = $1 THEN
RETURN QUERY SELECT.....
ELSE
RAISE NOTICE 'Not Worked';
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;

Error: VARIADIC parameter must be the last input parameter.

In SQL code im supposed to use 6 parameters. Please update me with sample code.

How are you expecting to call this function? Can you give an example? — Richard Huxton, Oct 09 '13 at 15:01
@RichardHuxton I am not sure but My Exception is something like this `select * from ip_source_test('Source Member','week','2012-09-01','2013-09-01',VARIADIC '{128,270}'::int[],VARIADIC '{AJ,alexandra}'::text[])` — Naveen, Oct 09 '13 at 15:15
This is a follow-up to: http://stackoverflow.com/questions/19202832/pass-multiple-values-in-single-parameter — Erwin Brandstetter, Oct 09 '13 at 15:33
Logically, there could only be one variadic parameter, since it refers to an unknown number of additional arguments. You would invoke the function as `ip_source_test(a,b,c,d,e,f,g,h,i,j)`, where `a` through `d` are the first four arguments, and the rest are gathered up in the last variadic parameter. To expect the function to know how the remaining six arguments are to be distributed is beyond its psychic powers. — Manngo, Apr 06 '18 at 06:29

score 1 · Answer 1 · answered Oct 09 '13 at 15:32

1

There can be only one VARIADIC per function, since variadic encompasses all the other arguments passed by the caller.

If you mean for the caller to use arrays, there's no point in using variadic anyway, the function signature could look like:

CREATE FUNCTION ip_source_test(text,text,date,date,int[], text[])

answered Oct 09 '13 at 15:32

Daniel Vérité

58,074
15
129
156

`VARIADIC` does not encompass *all* other arguments. There can be other parameters before that one. – Erwin Brandstetter Oct 09 '13 at 15:41
1

By _other_ arguments I mean the ones beyond those specified in the function declaration. My understanding is that both our answers are saying exactly the same thing. – Daniel Vérité Oct 09 '13 at 15:58

Erwin Brandstetter · Answer 2 · 2022-05-29T22:26:39.293

1

The error message tells you:

VARIADIC parameter must be the last input parameter.

It follows logically that a function can only take a single VARIADIC parameter. There can be other (non-VARIADIC) parameters before that one. The manual:

Effectively, all the actual arguments at or beyond the VARIADIC position are gathered up into a one-dimensional array

edited May 29 '22 at 22:26

answered Oct 09 '13 at 15:38

Erwin Brandstetter

605,456
145
1,078
1,228

VARIADIC parameter must be the last input parameter

2 Answers2

Linked