How to write function for optional parameters in postgresql?

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 textas$$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

Apart of the VARIADIC option pointed by @a_horse_with_no_name, which is only a syntax sugar for passing an array with any number of elements of the same type, you can't define a function with optional parameters because, in postgres, functions are identified not only by its name but also by its arguments and the types of them.

That is: create function foo (int) [...] and create function foo (varchar) [...] will create different functions.

Which is called when you execute, for example, select foo(bar) depends on bar data type itself. That is: if it is an integer, you will call the first one and if it is varchar, then second one will be called.

More than that: if you execute, for example, select foo(now()), then a function not exists exception will be triggered.

So, as I said, you can't implement functions with variable arguments, but you can implement multiple functions with the same name and distinct argument (an/or type) sets returning the same data type.

If you (obviously) doesn't want to implement the function twice, the only thing you need to do is to implement a "master" function with all possible parameters and the others (which have fewer parameters) only calling the "master" one with default values for the non received parameters.

As an option, I got a function i tested with Navicat App:CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"(sponsor_name varchar default 'Save the Children')It generates me this. (Note: Please look at the parameter difference)CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"("sponsor_name" varchar='Save the Children'::character varying)

 CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"("sponsor_name" varchar='Save the Children'::character varying)  RETURNS "pg_catalog"."void" AS $BODY$DECLARE    rec RECORD;BEGIN    FOR rec IN SELECT      companies."name" AS org_name,      "sponsors"."name" AS sponsor_name      FROM      "donor_companies"      JOIN "sponsors"      ON "donor_companies"."donor_id" = "sponsors"."id"       JOIN companies      ON "donor_companies"."organization_id" = companies."id"      WHERE      "public"."sponsors"."name" = sponsor_name    LOOP    RAISE NOTICE '%', rec.org_name;  END LOOP;END;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;