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.

I want to use and,or conditions in my function.

indu
– indu

2016-10-07 05:28:44 +00:00
Commented Oct 7, 2016 at 5:28 — indu
– indu, Commented Oct 7, 2016 at 5:28

score 176 · Accepted Answer · 2017-08-31 06:14:36Z

176

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

edited Aug 31, 2017 at 6:14

answered Oct 7, 2016 at 6:18

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

indu Over a year ago

can i use select Statement instead of return format in above function?.If i use select statement i am getting error like "query has no destination for result data"

user330315 Over a year ago

@indu: that is a totally different question which is answered in the manual: postgresql.org/docs/current/static/…

bitifet · Accepted Answer · 2016-10-07 05:59:38Z

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.

Kiry Meas · Accepted Answer · 2019-08-23 03:30:58Z

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;

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-27 17:51:52Z

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

Collectives™ on Stack Overflow

How to write function for optional parameters in PostgreSQL?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related