Function Default Parameters - SQL Server to PostgreSQL Migration

In SQL Server, you can define a user-defined function with default parameter values, but you must specify DEFAULT keyword if you skip the parameter values when you call the function.

In PostgreSQL, you can omit the parameter to apply its default value, and the DEFAULT keyword is not allowed.

SQL Server:

  -- Function with default parameters
  CREATE FUNCTION sum1(@param1 INT = 1, @param2 INT = 1)
  RETURNS INT
  BEGIN
    RETURN @param1 + @param2
  END
  GO
 
  -- Calling the function with default parameters
  SELECT dbo.sum1(DEFAULT, DEFAULT);
  /* Result: 2 */
 
  -- () syntax is not allowed
  SELECT dbo.sum1();
  /* Msg 313, Level 16, State 2, Line 1 */
  /*  An insufficient number of arguments were supplied for the procedure or function dbo.sum1. */

PostgreSQL:

  -- Function with default parameters
  CREATE OR REPLACE FUNCTION sum1(p_param1 INT = 1, p_param2 INT = 1)
  RETURNS INT
  AS $$
  BEGIN
    RETURN p_param1 + p_param2;
  END;
  $$ LANGUAGE plpgsql;
 
  -- Try to use DEFAULT keyword
  SELECT sum1(DEFAULT, DEFAULT);
  /* ERROR:  DEFAULT is not allowed in this context */
 
  SELECT sum1();
  /* Result: 2 */

For more information, see SQL Server to PostgreSQL Migration.