Postgresql – POSTGRES 9.4: Function parameter as empty array

arrayparameterpostgresql

I'm trying to create an empty array as a parameter so it's optional. I want to be able to call it without having the need to give a 2nd and 3rd parameter. Can somebody help?

CREATE OR REPLACE FUNCTION myschema.fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type DEFAULT[] '{}'::par_type[]
)
RETURNS void AS

When I call it like this:

select * from myschema.fnc_calc('what_the_hell');

I get this message:

ERROR:  function myschema.fnc_calc(unknown, unknown, par_type) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

BTW, this is has nothing to do with the public schema or calling it from a different schema. All the references are right. I checked this. I'm thinking that the declaration of the array parameter (p_par3) in the function should be something different (maybe not subscribe to an array with the '[]', but leaving it out doesn't work as it requires the parameter to be populated). Any ideas?

Best Answer

Your p_par3 is defined as par_type which is not the declaration for an array. You need to define it as par_type[] if that should be a parameter that is an array.

The following works for me: 

CREATE OR REPLACE FUNCTION fnc_calc(
    p_par1 text,
    p_par2 text DEFAULT NULL::text,
    p_par3 par_type[] DEFAULT '{}'::par_type[]
)
RETURNS integer AS
$$
  select 42;
$$ 
language sql;

Also: default[] is wrong - your whole statement will not work.

Related Question