postgresqlpostgresql-functions

Why Does Adding an (Un-used) Argument to my PostgreSQL Function Make it Return Null?

I have a PostgreSQL function:

CREATE OR REPLACE FUNCTION test()
RETURNS some_table
LANGUAGE sql STABLE STRICT SECURITY DEFINER
AS $function$
  SELECT * FROM some_table ;
$function$;

It works great, and returns records from some_table as expected. However, if I write the exact same query, except I add an optional argument:

CREATE OR REPLACE FUNCTION test(arg VARCHAR DEFAULT NULL)

The function returns a single result of:

null | null | null | null | null | null

Can anyone explain why? As I understand it, an un-used argument (especially one with a DEFAULT) shouldn't even have any impact on the function or its query.

Solution

  • According to the documentation:

    STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

    Note that the strict parameter for functions with no arguments has no effect.