Postgresql – Why does this scalar function not raise an error

plpgsqlpostgresql

My understanding is that scalar functions always return a row, but I don't understand why the first of these functions does not raise an exception while the second does. The only difference is the second accepts an additional parameter.

The first one always returns a row of NULL.

create schema app_testbed;

create table app_testbed.items(
    id serial primary key,
    name varchar
);

-- why does the "other" parameter change how it works?
create or replace function app_testbed.this_should_break_but_doesnt(
  item app_testbed.items,
  other text default null 
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Does not raise.';
  end;
$$ language plpgsql strict volatile;

-- without the additional parameter it raises as expected
create or replace function app_testbed.this_breaks(
  item app_testbed.items
)
returns app_testbed.items as $$
  declare
    v_item app_testbed.items;
  begin
    raise exception 'Raises.';
  end;
$$ language plpgsql strict volatile;

Best Answer

Figured it out myself. I did not fully understand how the strict keyword works but the docs state:

RETURNS NULL ON NULL INPUT or 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.