1

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.

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 can find the best candidate for untyped string literals without ambiguity - as long as the function is not overloaded:

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

Or with an 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, so you don't need the array wrapper. (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:

  • @DarthKangooroo: If that answers your question, consider accepting it. – Erwin Brandstetter Dec 17 '18 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.