0

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?

  • You wouldn't get and error message like that when calling the function with a single parameter. And the function definition has a syntax error. Please fix your question. Present your actual function definition and call. – Erwin Brandstetter Aug 29 '16 at 10:24
1

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.

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.