Reputation: 1197
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.
Upvotes: 98
Views: 113298
Reputation: 1
You can set a default value to IN
, INOUT
and VARIADIC
parameters as shown below. *The parameter with or without IN
is an IN
parameter:
CREATE FUNCTION my_func(
IN num1 INTEGER DEFAULT 1,
INOUT num2 INTEGER DEFAULT 2,
VARIADIC nums INTEGER[] DEFAULT ARRAY[3,4]
)
AS $$
BEGIN
SELECT num1 + num2 + nums[1] + nums[2] INTO num2;
END;
$$ LANGUAGE plpgsql;
Or:
CREATE FUNCTION my_func(
IN INTEGER DEFAULT 1,
INOUT INTEGER DEFAULT 2,
VARIADIC INTEGER[] DEFAULT ARRAY[3,4]
)
AS $$
BEGIN
SELECT $1 + $2 + $3[1] + $4[2] INTO $2;
END;
$$ LANGUAGE plpgsql;
Then, calling my_func()
returns 5
as shown below:
postgres=# SELECT my_func();
my_func
---------
10
(1 row)
Be careful, setting a default value to an OUT
parameter gets the error below:
CREATE FUNCTION my_func(OUT num INTEGER DEFAULT 2)
AS $$ -- ↑↑↑ ↑ ↑ ↑ ↑ ↑
BEGIN
END;
$$ LANGUAGE plpgsql;
ERROR: only input parameters can have default values
Upvotes: 0
Reputation: 1242
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;
Upvotes: 0
Reputation:
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
Upvotes: 171
Reputation: 3679
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.
Upvotes: 6