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