How to write function for optional parameters in PostgreSQL?

Question

My requirement is write optional parameters to a function. Parameters are optional sometimes I will add or I will not pass parameters to function. Can anyone help me how to write function?

I am writing like:

SELECT * 
FROM test 
WHERE field3 IN ('value1','value2') 
AND ($1 IS null OR field1 = $1) 
AND ($2 IS null OR field2 = $2) 
AND ($3 IS null OR field3 = $3);

I am passing parameters to Query but my output is not expected.when I pass all three parameters my output is correct,otherwise it is not expected output.

Answer 1

You can define optional parameters by supplying a default value.

create function foo(p_one integer default null, 
                    p_two integer default 42, 
                    p_three varchar default 'foo')
  returns text
as
$$
begin
    return format('p_one=%s, p_two=%s, p_three=%s', p_one, p_two, p_three);
end;
$$
language plpgsql;

You can "leave out" parameters from the end, so foo(), foo(1) or foo(1,2) are valid. If you want to only supply a parameter that is not the first you have to use the syntax that specifies the parameter names.

select foo(); 

returns: p_one=, p_two=42, p_three=foo

select foo(1); 

returns: p_one=1, p_two=42, p_three=foo

select foo(p_three => 'bar')

returns: p_one=, p_two=42, p_three=bar