Postgresql – How to specify the last parameters in a function call and let the others default to the DEFAULT in the declaration

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

1. Create a new, blank database for our tests:

   USE master;
   IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
   BEGIN
       ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
       DROP DATABASE mv;
   END
   GO
   CREATE DATABASE mv;
   GO
   

2. Create a table, a multi-statement function, and a table-valued-function:

   USE mv;
   GO
   CREATE TABLE dbo.t
   (
       t_id int NOT NULL
           CONSTRAINT PK_t
           PRIMARY KEY CLUSTERED
   );
   GO
   
   CREATE FUNCTION dbo.t_func
   (
       @t_id int
   )
   RETURNS bit
   WITH SCHEMABINDING
   AS
   BEGIN
       DECLARE @r bit;
       IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
           SET @r = 1
       ELSE
           SET @r = 0;
       RETURN @r;
   END
   GO
   
   CREATE FUNCTION dbo.t_tvf
   (
       @min_t_id int
       , @max_t_id int
   )
   RETURNS TABLE 
   WITH SCHEMABINDING
   AS
   RETURN (
       SELECT t_id = t.t_id
           , e = dbo.t_func(dbo.t.t_id)
       FROM dbo.t
       WHERE t.t_id >= @min_t_id
           AND t.t_id <= @max_t_id
   );
   GO
   

3. Insert some sample data into the table:

   INSERT INTO dbo.t (t_id)
   SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
   FROM sys.syscolumns c;
   GO
   

4. Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

   CREATE TABLE dbo.function_stats
   (
       run_num int NOT NULL
       , object_name sysname NOT NULL
       , execution_count int NULL 
       , CONSTRAINT PK_function_stats
           PRIMARY KEY CLUSTERED (run_num, object_name)
   );
   GO
   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 1
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   GO
   

5. Run a query against the TVF:

   SELECT t.*
   FROM dbo.t_tvf(1, 2) t;
   GO
   

6. Capture the execution stats now:

   INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
   SELECT 2
       , o.name
       , COALESCE(fs.execution_count, 0)
   FROM sys.objects o 
       LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
   WHERE o.name = 't_func';
   

7. The function stats results:

   SELECT *
   FROM dbo.function_stats fs
   ORDER BY fs.run_num
       , fs.object_name;
   

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

If you don't always pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL:

CREATE OR REPLACE FUNCTION func(_a int  = NULL
                              , _b text = NULL
                              , _c text = NULL)
  RETURNS TABLE(...) AS
$func$
    SELECT ... FROM ...
    WHERE (speed = $1 OR $1 IS NULL)
    AND   (stop  = $2 OR $2 IS NULL)
    ...
$func$  LANGUAGE sql;

Now you can call the function with any number of parameters using named notation:

SELECT * FROM func(_c => 'foo', _a => 123);  -- no _b, which defaults to NULL

Note, the assignment operator in the call is => (or := for Postgres 9.4 or older), not =!
See:

• The forgotten assignment operator “=” and the commonplace “:=”
• Functions with variable number of input parameters

Aside: "string" is not a data type, text is.

Much more is possible with dynamic SQL with EXECUTE in a plpgsql function.

More comprehensive answer on SO with complete recipes:

• Test for is null or = value in function