How to pass an array to a plpgsql function with VARIADIC parameter

Question

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.

I have already defined the function

CREATE OR REPLACE FUNCTION mix_table_fields(input_table_name VARCHAR, output_table_name VARCHAR, VARIADIC field_names VARCHAR[])
[...]

Now, when I try to call it:

SELECT mix_table_fields('articles'::VARCHAR, 'output_random_articles'::VARCHAR, array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]) ;

I get the following error : " the function mix_table_fields(character varying, character varying, character varying[]) does not exist".

I have had this error before. It cannot recognize the signature of the function (its input parameters).

When I use pgAdmin3 to look at the function definition, it is:

CREATE OR REPLACE FUNCTION public.mix_table_fields(
    IN input_table_name character varying,
    IN output_table_name character varying,
    VARIADIC field_names character varying[])

I cannot see what's wrong.

And if there is a way of getting rid of the ::VARCHAR at each parameter when calling the function, it would be easier.

Answer 1

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call:

SELECT mix_table_fields('art'::VARCHAR
                      , 'out'::VARCHAR
                      , VARIADIC array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]);

Even works without explicit type casts in your case. Function type resolution finds the best match as long as there is no overloaded variant only differing in (also compatible) parameter types. Call with array literal:

SELECT mix_table_fields('art', 'out', VARIADIC '{type,colour,size,price}');

Without the keyword VARIADIC, Postgres expects a list of element values like:

SELECT mix_table_fields('art', 'out', 'type', 'colour', 'size', 'price');

... which is the whole point of a VARIADIC function parameter: to be able to call it with a variable number of parameters of the element type. (Also the reason why a VARIADIC parameter must come last in the parameter list: else the call could be ambiguous.) If you don't need this feature, use a plain varchar[] parameter without VARIADIC in the function definition to begin with.

Related:

• How to use an array as argument to a VARIADIC function in PostgreSQL?
• Make query depend on input in PostgreSQL function